package service

import (
	"encoding/json"
	"errors"
	"fmt"
	ClickHouse "gin/database"
	"gin/model"
	"gin/types"
	"github.com/gin-gonic/gin"
	teamonesHelper "github.com/teamones-open/go-helper"
	"gorm.io/gorm"
	"log"
	"strconv"
	"strings"
	"time"
)

type Workbench struct {
}

// GetPlanDoneEpisodeStepCategory 获得排期完成的剧集和环节信息
func (w *Workbench) GetPlanDoneEpisodeStepCategory(projectId int64, startTime, endTime int64, categoryIdList []int64) (res []PlanEpisodeStepCategory, err error) {
	var reqStruct = map[string]interface{}{"project_id": projectId, "start_time": startTime, "end_time": endTime, "category_ids": categoryIdList}
	reqStr, _ := json.Marshal(reqStruct)
	rk := fmt.Sprintf("workbench:%d:GetPlanDoneEpisodeStepCategory-%d-%d-%s", projectId, startTime, endTime, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()
	// 查询排期要完成的剧集和环节
	pq := ClickHouse.DB.Model(&model.Plan{}).
		Joins("JOIN task_relation_tag r_step_category on link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id").
		Where("plan.scope = 'single' and plan.type = 'step_category'")
	if projectId > 0 {
		pq = pq.Where("plan.project_id = ?", projectId)
	}
	if startTime > 0 {
		pq = pq.Where("plan.end_time >= ?", startTime)
	}
	if endTime > 0 {
		pq = pq.Where("plan.end_time <= ?", endTime)
	}
	if len(categoryIdList) > 0 {
		pq = pq.Where("r_step_category.link_id in ?", categoryIdList)
	}
	err = pq.Group("plan.entity_id,r_step_category.link_id").
		Select("plan.entity_id as episode_id,r_step_category.link_id as step_category_id,max(plan.end_time) max_end_time").
		Find(&res).Error

	if err != nil {
		return res, err
	}

	return
}

// getOverdueTaskCount 逾期任务数
func (w *Workbench) getOverdueTaskCount(req types.GlobalAbnormalCountReq, saasUserId int64, tenantId int64, rc chan types.GlobalAbnormalCountResItemAsync, code string) {
	var err error
	var res types.GlobalAbnormalCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("task.end_time = 0 and task.plan_end_time < ?", time.Now().Unix()).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.GlobalAbnormalCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getGroupOverdueTaskCount 组内逾期任务数
func (w *Workbench) getGroupOverdueTaskCount(req types.GlobalAbnormalCountReq, userId int64, saasUserId int64, tenantId int64, rc chan types.GlobalAbnormalCountResItemAsync, code string) {
	var err error
	var res types.GlobalAbnormalCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		rc <- res
	}()
	var statusList []model.Status
	err = ClickHouse.DB.Model(&model.Status{}).Where("code not in ('final','closed')").Find(&statusList).Error
	if err != nil {
		return
	}
	var statusIdList []int64
	for _, status := range statusList {
		statusIdList = append(statusIdList, status.Id)
	}
	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")
	if err != nil {
		return
	}
	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		err = errors.New("departmentId not find")
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("task.tenant_id = ?", tenantId).
		Where("task.department_id IN ?", departmentId).
		Where("task.end_time = 0 and task.plan_end_time < ? ", time.Now().Unix()).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.GlobalAbnormalCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getWaitExecRepairOrderCount 待处理工单
func (w *Workbench) getPendingTaskWorkOrderCount(req types.GlobalAbnormalCountReq, saasUserId int64, tenantId int64, rc chan types.GlobalAbnormalCountResItemAsync, code string) {
	var res types.GlobalAbnormalCountResItemAsync
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	db := ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Joins("join task on task.id = task_work_order.task_id join task_work_order_user on task_work_order_user.work_order_id = task_work_order.id")

	status, err := model.GetStatusByCode("closed")
	if err != nil {
		return
	}
	if status.Id > 0 {
		db.Where("simpleJSONExtractUInt(task.json, 'task_status') != ?", status.Id)
	}

	if req.ProjectId > 0 {
		db = db.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.GlobalAbnormalCountResItemCountData, 0)
			return
		}
		db = db.Where("task.project_id in ?", imUserProjectIds)
	}

	//计划完成时间筛选
	if req.StartTime > 0 {
		db = db.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("task.plan_end_time <= ?", req.EndTime)
	}

	//工单状态范围限制
	options := model.Options{}
	untreatedString, err := options.GetConfigItem("task_work_order_status_config", "untreated")
	if err != nil {
		return
	}
	untreatedStatus := strings.Split(untreatedString, ",")

	err = db.Where("task_work_order.tenant_id = ? and task_work_order.status in (?) and task.step_type = 'step'", tenantId, untreatedStatus).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task_work_order_user.user_id = ? ", saasUserId).
		Select("task.project_id as project_id,count(distinct task_work_order.id) as number").Group("project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWorkbenchRepairTaskCount 返修任务数
func (w *Workbench) getWorkbenchRepairTaskCount(req types.GlobalAbnormalCountReq, saasUserId int64, tenantId int64, rc chan types.GlobalAbnormalCountResItemAsync, code string) {
	var err error
	var res types.GlobalAbnormalCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	var statusId int64
	err = ClickHouse.DB.Model(&model.Status{}).Where("code = 'remake'").Pluck("id", &statusId).Error
	if err != nil {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(`task`.json, 'task_status') = ?", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		userProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(userProjectIds) == 0 {
			res.CountItem.CountData = make([]types.GlobalAbnormalCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", userProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// GlobalAbnormalCount 全局异常统计
func (w *Workbench) GlobalAbnormalCount(userInfo types.UserInfoHeader, req types.GlobalAbnormalCountReq) (res types.GlobalAbnormalCountRes, err error) {
	resChan := make(chan types.GlobalAbnormalCountResItemAsync, len(req.QueryCodeList))
	for _, v := range req.QueryCodeList {
		switch v {
		case "workbench_overdue_task_group": // 组内逾期任务数量统计
			go w.getGroupOverdueTaskCount(req, userInfo.Id, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
			break
		case "workbench_overdue_task_personal": // 个人任务逾期任务数量统计
			go w.getOverdueTaskCount(req, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
			break
		case "workbench_pending_task_work_order": // 待处理工单
			go w.getPendingTaskWorkOrderCount(req, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
			break
		case "workbench_repair_task": // 返修任务
			go w.getWorkbenchRepairTaskCount(req, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
			break
		case "workbench_wait_assign_asset_task": // 待分配资产任务
			go w.getWorkbenchWaitAssignTaskCount(req.ProjectId, userInfo, "asset", resChan, v)
		case "workbench_wait_assign_shot_task": // 待分配镜头任务
			go w.getWorkbenchWaitAssignTaskCount(req.ProjectId, userInfo, "shot", resChan, v)
		case "workbench_file_wait_update_task":
			// todo 文件待更新任务
			resChan <- types.GlobalAbnormalCountResItemAsync{
				CountItem: types.GlobalAbnormalCountResItem{
					Code: v,
				},
			}
		default:
			resChan <- types.GlobalAbnormalCountResItemAsync{
				CountItem: types.GlobalAbnormalCountResItem{
					Code: v,
				},
			}
		}
	}
	for range req.QueryCodeList {
		resItem := <-resChan
		if resItem.Err != nil {
			// 错误日志记录
			log.Println(resItem.CountItem.Code, resItem.Err.Error())
		}
		res = append(res, resItem.CountItem)
	}

	return
}

// getWorkbenchWaitAssignTaskCount 待分配任务
func (w *Workbench) getWorkbenchWaitAssignTaskCount(projectId int64, userInfo types.UserInfoHeader, moduleCode string, rc chan types.GlobalAbnormalCountResItemAsync, code string) {
	var err error
	var res types.GlobalAbnormalCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var DepartmentService Department

	departmentStepCategoryIdList, err := DepartmentService.GetUserDepartmentStepCategoryIdList(userInfo.TenantId, userInfo.Id)
	if len(departmentStepCategoryIdList) <= 0 {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("task.step_type = 'step'").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.assign_status = 'default'").
		Where("simpleJSONExtractUInt(task.json, 'task_status') <> 7").
		Where("task.step_category_id in ?", departmentStepCategoryIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if moduleCode == "asset" {
		dbr.Where("task.entity_module_id in (select id from module where code in ('design','asset','level'))")
	} else if moduleCode == "shot" {
		dbr.Where("task.entity_module_id in (select id from module where code in ('shot'))")
	}

	if projectId > 0 {
		dbr.Where("task.project_id = ?", projectId)
	} else {
		userProjectIds, errs := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(userProjectIds) == 0 {
			res.CountItem.CountData = make([]types.GlobalAbnormalCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", userProjectIds)
	}
	err = dbr.Group("task.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWorkbenchWaitDoneTaskCount 待完成任务
func (w *Workbench) getWorkbenchWaitDoneTaskCount(req types.PersonalTaskCountReq, saasUserId int64, tenantId int64, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	todoModule := ModuleByCode("todo")
	// 执行人是我 但是任务状态为未完成的任务
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN task_member on task.id = task_member.task_id and task_member.user_id = ?", saasUserId).
		Select("count(distinct task.id) as number,task.project_id as project_id").
		Where("task.tenant_id = ?", tenantId).
		Where("(task.step_type = 'step' and simpleJSONExtractUInt(task.json, 'task_status') not in ( 3, 7 )) or (task.entity_module_id = ? and task_member.status not in ( 3, 7 )) ", todoModule.Id).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getWorkbenchWaitReviewTaskCount 待审核任务
func (w *Workbench) getWorkbenchWaitReviewTaskCount(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, moduleCode string, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// 审核人是我 并且当前到我审核
	dbr := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("JOIN review_request_node ON review_request_node.review_request_id = review_request.id "+
			"JOIN version on version.id = review_request.link_id and review_request.module_id = ? "+
			"JOIN task on task.id = version.link_id and version.module_id = ?", ModuleByCode("version").Id, ModuleByCode("task").Id).
		Select("count(review_request.id) as number,review_request.project_id as project_id").
		Where("review_request.module_id = ?", ModuleByCode("version").Id).
		Where("review_request.tenant_id = ?", userInfo.TenantId).
		Where("review_request_node.is_active = 'yes'").
		Where("review_request_node.plan_reviewer = ?", userInfo.SaasUserId)

	//if req.StartTime > 0 {
	//	dbr.Where("task.plan_end_time >= ?", req.StartTime)
	//}
	//if req.EndTime > 0 {
	//	dbr.Where("task.plan_end_time <= ?", req.EndTime)
	//}

	if moduleCode == "asset" {
		dbr.Where("task.entity_module_id in (select id from module where code in ('design','asset','level'))")
	} else if moduleCode == "shot" {
		dbr.Where("task.entity_module_id in (select id from module where code in ('shot'))")
	}

	if req.ProjectId > 0 {
		dbr.Where("review_request.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("review_request.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("review_request.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWorkbenchMyReviewingTaskCount 我的审核中的任务
func (w *Workbench) getWorkbenchMyReviewingTaskCount(req types.PersonalTaskCountReq, saasUserId int64, tenantId int64, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// 执行人是我 但是任务状态为未完成的任务
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("task.executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') in (select id from status where correspond = 'daily')").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getWaitSignFrameworkContractCount 待签订框架合同
func (w *Workbench) getWaitSignFrameworkContractCount(tenantId int64, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	taskService := Task{}
	BusinessMode, err := taskService.getBusinessMode()
	if err != nil {
		return
	}
	db := ClickHouse.DB.Model(&model.Supplier{})
	dbs := ClickHouse.DB
	switch BusinessMode {
	case "both_sides_mode":
		dbs = dbs.Where("supplier.framework_agreement_status = 'wait_for_sign'")
		break
	case "tripartite_mode":
		dbs = dbs.Where("tenant.framework_agreement_status = 'wait_for_sign'")
		break
	}

	err = db.
		Joins("JOIN tenant_authentication ata ON ata.tenant_id = supplier.from_tenant_id").
		Joins("JOIN tenant_authentication bta ON bta.tenant_id = supplier.tenant_id").
		Where("ata.status = 'active' and bta.status = 'active' and ata.fdd_cert_status = '1' and ata.fdd_auth_status = '4' and bta.fdd_cert_status = '1' and bta.fdd_auth_status = '4'").
		Where("supplier.type = 'outside' and (supplier.from_tenant_id = ? or supplier.tenant_id = ?)", tenantId, tenantId).
		Where(dbs).
		Select("count(distinct supplier.id) as number").
		Find(&res.CountItem.CountData).Error

	return
}

// getWorkbenchWaitAcceptOrderCount 待接受订单
func (w *Workbench) getWorkbenchWaitAcceptOrderCount(tenantId int64, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	err = ClickHouse.DB.Model(&model.Order{}).
		Where("`order`.status ='wait_for_party_b_ensure' and `order`.to_tenant_id =?", tenantId).
		Select("count(`order`.id) as number").Find(&res.CountItem.CountData).Error
	return
}

// getWorkbenchWaitDeliveryTaskCount 待交付任务
func (w *Workbench) getWorkbenchWaitDeliveryTaskCount(req types.PersonalTaskCountReq, saasUserId int64, tenantId int64, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// 排期计划完成而且执行人是我 但是任务状态为未完成的任务
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("task.executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') <> 7").                                                                       // 没有交付
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3"). // 没有废除
		Where("task.plan_delivery_time between ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("task.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWaitUploadFileTodoCount 统计等待上传的文件记录
func (w *Workbench) getWaitUploadFileTodoCount(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, rc chan types.PersonalTaskCountResItemAsync, code string) (res types.PersonalTaskCountResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	sub := ClickHouse.DB.Table("`order_task_upstream_version` otuv").
		Joins("JOIN version v on otuv.version_id = v.id "+
			"JOIN task on task.id = v.link_id").
		Where("otuv.from_tenant_id = ? and task.executor_type IN ('default', 'inside')", userInfo.TenantId).
		Select("max(v.id) as id").Group("v.link_id")

	if req.ProjectId > 0 {
		sub = sub.Where("v.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, err := GetImUserProjectIdListByRoleCode(userInfo.Id, userInfo.TenantId, []string{}, []string{"in_progress"})
		if err != nil {
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		sub = sub.Where("v.project_id in ?", imUserProjectIds)
	}
	err = ClickHouse.DB.Table("(?) as vm", sub).
		Joins("JOIN version v on v.id = vm.id").
		Where("v.is_uploaded = 'no'").
		Group("v.project_id").
		Select("count(distinct v.id) as number,v.project_id as project_id").
		//Select("count(v.id) as number").
		Find(&res.CountItem.CountData).
		Error

	return
}

// getWaitDownloadFileCount 统计等待下载的文件记录
func (w *Workbench) getWaitDownloadFileCount(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, rc chan types.PersonalTaskCountResItemAsync, code string) (res types.PersonalTaskCountResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	sub := ClickHouse.DB.Table("`order_task_file_list` otfl").
		Joins("JOIN order_task_upstream_version otuv on otfl.task_id = otuv.task_id and otfl.to_tenant_id = otuv.to_tenant_id "+
			"JOIN version v on otuv.version_id = v.id").
		Where("otfl.to_tenant_id = ? and v.is_uploaded = 'yes'", userInfo.TenantId, userInfo.TenantId).
		Select("max(otuv.id) as id").Group("v.link_id")

	if req.ProjectId > 0 {
		sub = sub.Where("otfl.to_project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, err := GetImUserProjectIdListByRoleCode(userInfo.Id, userInfo.TenantId, []string{}, []string{"in_progress"})
		if err != nil {
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		sub = sub.Where("otfl.to_project_id in ?", imUserProjectIds)
	}

	err = ClickHouse.DB.Table("(?) as vm", sub).
		Joins("JOIN order_task_upstream_version dl on dl.id = vm.id " +
			"JOIN order_task_file_list otfl on otfl.to_tenant_id = dl.to_tenant_id and otfl.task_id = dl.task_id").
		Where("dl.is_download = 'no'").
		Group("otfl.to_project_id").
		Select("count(distinct dl.id) as number,otfl.to_project_id as project_id").
		Find(&res.CountItem.CountData).
		Error
	return
}

// getWaitReviewDelayOrder 待审批延期单-审批人是我,但是没有审批通过
func (w *Workbench) getWaitReviewDelayOrder(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, rc chan types.PersonalTaskCountResItemAsync, code string) {

	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	dbr := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("JOIN delay_order_task ON delay_order_task.id = review_request.link_id").
		Joins("JOIN review_request_node ON review_request_node.review_request_id = review_request.id").
		Select("count(distinct delay_order_task.delay_order_id) as number,review_request.project_id as project_id").
		Where("review_request.module_id = ?", ModuleByCode("delay_order_task").Id).
		Where("review_request.tenant_id = ?", userInfo.TenantId).
		Where("review_request_node.is_active = 'yes'").
		Where("review_request.is_pass = 'no'").
		Where("review_request_node.plan_reviewer = ?", userInfo.SaasUserId)

	if req.ProjectId > 0 {
		dbr.Where("review_request.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("review_request.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("review_request.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getNotReviewDelayOrder 未审批延期单 - 当前用户创建的
func (w *Workbench) getNotReviewDelayOrder(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, rc chan types.PersonalTaskCountResItemAsync, code string) {

	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	dbr := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("JOIN delay_order_task ON delay_order_task.id = review_request.link_id").
		Joins("JOIN review_request_node ON review_request_node.review_request_id = review_request.id").
		Select("count(distinct delay_order_task.delay_order_id) as number,review_request.project_id as project_id").
		Where("review_request.module_id = ?", ModuleByCode("delay_order_task").Id).
		Where("review_request.tenant_id = ?", userInfo.TenantId).
		Where("review_request_node.is_active = 'yes'").
		Where("review_request.is_pass = 'no' and review_request.is_reviewed = 'no'").
		Where("review_request.created_by = ?", userInfo.SaasUserId)

	if req.ProjectId > 0 {
		dbr.Where("review_request.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("review_request.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("review_request.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWorkbenchWaitConfirmPlan 待我确认的排期
func (w *Workbench) getWorkbenchWaitConfirmPlan(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// 审核人是我 并且当前到我审核
	dbr := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("JOIN review_request_node ON review_request_node.review_request_id = review_request.id "+
			"JOIN plan on plan.id = review_request.link_id and review_request.module_id = ? ", ModuleByCode("plan").Id).
		Select("count(review_request.id) as number,review_request.project_id as project_id").
		Where("review_request.module_id = ?", ModuleByCode("plan").Id).
		Where("review_request.tenant_id = ?", userInfo.TenantId).
		Where("review_request_node.is_active = 'yes'").
		Where("review_request_node.plan_reviewer = ?", userInfo.SaasUserId)

	//if req.StartTime > 0 {
	//	dbr.Where("review_request_node.confirm_end_time >= ?", req.StartTime)
	//}
	if req.EndTime > 0 {
		dbr.Where("review_request_node.confirm_end_time <= ?", req.EndTime)
	}

	if req.ProjectId > 0 {
		dbr.Where("review_request.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("review_request.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("review_request.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWorkbenchWaitReviewTaskDelay 待我审核的任务延期
func (w *Workbench) getWorkbenchWaitReviewTaskDelay(req types.PersonalTaskCountReq, userInfo types.UserInfoHeader, rc chan types.PersonalTaskCountResItemAsync, code string) {
	var err error
	var res types.PersonalTaskCountResItemAsync
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// 审核人是我 并且当前到我审核
	dbr := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("JOIN review_request_node ON review_request_node.review_request_id = review_request.id "+
			"JOIN task on task.id = review_request.link_id and review_request.module_id = ? ", ModuleByCode("task").Id).
		Select("count(review_request.id) as number,review_request.project_id as project_id").
		Where("review_request.module_id = ?", ModuleByCode("task").Id).
		Where("review_request.tenant_id = ?", userInfo.TenantId).
		Where("review_request_node.is_active = 'yes'")
	//.
	//	Where("review_request_node.plan_reviewer = ?", userInfo.SaasUserId)

	//if req.StartTime > 0 {
	//	dbr.Where("review_request_node.confirm_end_time >= ?", req.StartTime)
	//}
	//if req.EndTime > 0 {
	//	dbr.Where("review_request_node.confirm_end_time <= ?", req.EndTime)
	//}

	if req.ProjectId > 0 {
		dbr.Where("review_request.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.PersonalTaskCountResItemCountData, 0)
			return
		}
		dbr.Where("review_request.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("review_request.project_id").Find(&res.CountItem.CountData).Error
	return
}

// PersonalTaskCount 个人任务统计
func (w *Workbench) PersonalTaskCount(userInfo types.UserInfoHeader, req types.PersonalTaskCountReq) (res types.PersonalTaskCountRes, err error) {
	resChan := make(chan types.PersonalTaskCountResItemAsync, len(req.QueryCodeList))
	if err != nil {
		return
	}
	for _, v := range req.QueryCodeList {
		switch v {
		case "workbench_wait_done_task": // 待完成任务
			go w.getWorkbenchWaitDoneTaskCount(req, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
		case "workbench_wait_review_asset_task": // 我的资产审核任务
			go w.getWorkbenchWaitReviewTaskCount(req, userInfo, "asset", resChan, v)
		case "workbench_wait_review_shot_task": // 我的镜头审核任务
			go w.getWorkbenchWaitReviewTaskCount(req, userInfo, "shot", resChan, v)
		case "workbench_my_reviewing_task": // 我的审核中任务
			go w.getWorkbenchMyReviewingTaskCount(req, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
		case "workbench_wait_sign_framework_contract": // 待签订框架合同
			go w.getWaitSignFrameworkContractCount(userInfo.TenantId, resChan, v)
		case "workbench_wait_accept_order": // 待接受订单
			go w.getWorkbenchWaitAcceptOrderCount(userInfo.TenantId, resChan, v)
		case "workbench_wait_plan_content": // 待排期内容
			// todo
			resChan <- types.PersonalTaskCountResItemAsync{
				CountItem: types.PersonalTaskCountResItem{
					Code: v,
				},
			}
		case "workbench_wait_delivery_task": // 待交付任务
			go w.getWorkbenchWaitDeliveryTaskCount(req, userInfo.SaasUserId, userInfo.TenantId, resChan, v)
		case "workbench_wait_confirm_plan": // 待我确认的排期
			go w.getWorkbenchWaitConfirmPlan(req, userInfo, resChan, v)
		case "workbench_wait_review_task_delay": // 待我审核的延期
			go w.getWorkbenchWaitReviewTaskDelay(req, userInfo, resChan, v)
		case "wait_upload_file": // 待上传文件
			go w.getWaitUploadFileTodoCount(req, userInfo, resChan, v)
		case "wait_download_file": // 待下载
			go w.getWaitDownloadFileCount(req, userInfo, resChan, v)
		case "wait_review_delay_order": // 待审批的延期单
			go w.getWaitReviewDelayOrder(req, userInfo, resChan, v)
		case "not_review_delay_order": // 未审批的延期单
			go w.getNotReviewDelayOrder(req, userInfo, resChan, v)
		default:
			resChan <- types.PersonalTaskCountResItemAsync{
				CountItem: types.PersonalTaskCountResItem{
					Code: v,
				},
			}
		}
	}
	for range req.QueryCodeList {
		resItem := <-resChan
		if resItem.Err != nil {
			// 错误日志记录
			log.Println(resItem.CountItem.Code, resItem.Err.Error())
		}
		res = append(res, resItem.CountItem)
	}

	return
}

// getTaskPlanStepCategoryTaskCount 查询计划完成任务 环节统计
func (w *Workbench) getTaskPlanStepCategoryTaskCount(req types.StepCategoryTaskCountReq, userInfo types.UserInfoHeader, stepCategoryIdList []int64) (res map[string]types.TaskCountDataItem, err error) {

	var reqStruct = map[string]interface{}{"req": req, "tenant_id": userInfo.TenantId, "step_category_id": stepCategoryIdList}
	reqStr, _ := json.Marshal(reqStruct)
	rk := fmt.Sprintf("workbench:%d:getTaskPlanStepCategoryTaskCount-%s", req.ProjectId, teamonesHelper.GenerateMd5(string(reqStr)))

	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return res, err
		}
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Select("count(simpleJSONExtractInt(json, 'task_status') = 7 or null) done, count(id) total,r_step_category.link_id as id,task.step_type as type").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type <> 'step_category'").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if len(stepCategoryIdList) > 0 {
		dbr = dbr.Where("r_step_category.link_id IN ?", stepCategoryIdList)
	}

	dbr = dbr.Where("task.plan_end_time BETWEEN ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, err := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if err != nil || len(imUserProjectIds) == 0 {
			return res, err
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	var qr []types.TaskTypeCountQueryCount
	err = dbr.Group("r_step_category.link_id,task.step_type").Find(&qr).Error

	res = make(map[string]types.TaskCountDataItem)

	var taskType = "step"
	for _, count := range qr {
		taskType = "step"
		if count.Type != "step" {
			taskType = "todo"
		}
		res[fmt.Sprintf("%d-%s", count.Id, taskType)] = types.TaskCountDataItem{
			Done:  count.Done + res[fmt.Sprintf("%d-%s", count.Id, taskType)].Done,
			Total: count.Total + res[fmt.Sprintf("%d-%s", count.Id, taskType)].Total,
		}
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return res, err
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return res, err
	}
	return
}

// getPlanStepCategoryTaskCount 查询计划完成任务 环节统计
func (w *Workbench) getPlanStepCategoryTaskCount(req types.StepCategoryTaskCountReq, userInfo types.UserInfoHeader, stepCategoryIdList []int64, pqRes []PlanEpisodeStepCategory) (res map[string]types.TaskCountDataItem, err error) {
	if len(pqRes) == 0 {
		return
	}

	var reqStruct = map[string]interface{}{"req": req, "tenant_id": userInfo.TenantId, "step_category_id": stepCategoryIdList, "pq_res": pqRes}
	reqStr, _ := json.Marshal(reqStruct)
	rk := fmt.Sprintf("workbench:%d:GetPlanDoneEpisodeStepCategory-%d-%d-%s", req.ProjectId, req.StartTime, req.EndTime, teamonesHelper.GenerateMd5(string(reqStr)))

	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return res, err
		}
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Select("count(simpleJSONExtractInt(json, 'task_status') = 7 or null) done, count(id) total,r_step_category.link_id as id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type <> 'step_category'").
		Where("task.step_category_id IN ?", stepCategoryIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	epStepCategoryWhere := ClickHouse.DB
	for _, re := range pqRes {
		epStepCategoryWhere = epStepCategoryWhere.Or("r_step_category.link_id = ? and task.episode_id = ?", re.StepCategoryId, re.EpisodeId)
	}
	dbr = dbr.Where(epStepCategoryWhere)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	} else {
		imUserProjectIds, err := GetUserProjectIdListByRoleCode(userInfo.SaasUserId, userInfo.TenantId, []string{}, []string{"in_progress"})
		if err != nil || len(imUserProjectIds) == 0 {
			return res, err
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	var qr []types.TaskTypeCountQueryCount

	err = dbr.Group("r_step_category.link_id,task.step_type").Find(&qr).Error
	res = make(map[string]types.TaskCountDataItem)
	var taskType = "step"
	for _, count := range qr {
		taskType = "step"
		if count.Type != "step" {
			taskType = "todo"
		}
		res[fmt.Sprintf("%d-%s", count.Id, taskType)] = types.TaskCountDataItem{
			Done:  count.Done + res[fmt.Sprintf("%d-%s", count.Id, taskType)].Done,
			Total: count.Total + res[fmt.Sprintf("%d-%s", count.Id, taskType)].Total,
		}
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}

	return
}

// StepCategoryTaskCount 环节完成交付情况
func (w *Workbench) StepCategoryTaskCount(ctx *gin.Context, req types.StepCategoryTaskCountReq, userInfo types.UserInfoHeader) (res []types.StepCategoryTaskCountResItem, err error) {

	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}

	var stepCategoryIdList []int64
	var shotMotionTaskCountStepCategoryIdList []int64
	projectShowStepCategoryMap := make(map[int64]string)

	for _, datum := range projectShowStepCategoryList.Data {
		projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName

		stepCategoryIdList = append(stepCategoryIdList, int64(datum.ID))
		if datum.ShowName == "动画" {
			shotMotionTaskCountStepCategoryIdList = append(shotMotionTaskCountStepCategoryIdList, int64(datum.ID))
		}
	}

	//var showStepCategoryIdList []int
	var resNameMap = make(map[string]*types.StepCategoryTaskCountResItem)

	if err != nil || len(stepCategoryIdList) == 0 {
		return
	}
	// 查询计划
	pqRes, err := w.GetPlanDoneEpisodeStepCategory(req.ProjectId, req.StartTime, req.EndTime, stepCategoryIdList)
	if err != nil {
		return
	}
	var shotMotionPlanDoneGrouped = make(map[int64][]int64)
	var plistGrouped = make(map[int64][]int64)
	//var plistMaxEndTime = make(map[string]int64)
	for _, re := range pqRes {
		currentEpisodeId := re.EpisodeId
		categoryId := re.StepCategoryId
		if projectShowStepCategoryMap[categoryId] == "动画" {
			shotMotionPlanDoneGrouped[categoryId] = append(shotMotionPlanDoneGrouped[categoryId], currentEpisodeId)
		}
		plistGrouped[categoryId] = append(plistGrouped[categoryId], currentEpisodeId)
		//k := fmt.Sprintf("%d-%d", categoryId, currentEpisodeId)
		//plistMaxEndTime[k] = re.MaxEndTime
	}
	// 剧集封板情况统计
	episodePlanSealCount, err := w.getEpisodePlanSealCount(req.ProjectId, req.StartTime, req.EndTime)
	if err != nil {
		return
	}
	// 剧集环节配置完成情况
	episodeStepCategoryPlanSealTimeCount, err := w.getEpisodeStepCategoryPlanSealTimeCount(req, stepCategoryIdList)
	if err != nil {
		return
	}
	// 计划完成统计 完成
	taskPlanDataCount, err := w.getTaskPlanStepCategoryTaskCount(req, userInfo, stepCategoryIdList)
	if err != nil {
		return
	}
	// 排期完成统计 交付
	planDataCount, err := w.getPlanStepCategoryTaskCount(req, userInfo, stepCategoryIdList, pqRes)
	if err != nil {
		return
	}
	// 工单数量统计
	//taskWorkOrderCount, err := w.getStepCategoryTaskWorkOrderCount(req)
	//if err != nil {
	//	return
	//}
	// 计划/交付异常情况
	abnormalStepCategoryTaskList, err := w.getAbnormalTaskCount(req, plistGrouped)
	// 导演组的完成情况统计
	directorTaskCount, err := w.getDirectorTaskCount(req.ProjectId, req.StartTime, req.EndTime, true, false)
	if err != nil {
		return
	}
	projectTaskProgress := ProjectTaskProgress{}
	shotMotionTaskCount, err := projectTaskProgress.GetShotMotionTaskDeliveryCount(req.ProjectId, req.StartTime, req.EndTime, shotMotionTaskCountStepCategoryIdList, shotMotionPlanDoneGrouped)
	if err != nil {
		return
	}
	// 组装数据
	var moduleCodeMap = make(map[string]bool)
	resIndex := 0
	var waitDone int64 = 0
	var waitDoneTotal int64 = 0
	var waitDelivery int64 = 0
	var waitDeliveryTotal int64 = 0
	var abnormalTotal int64 = 0
	var abnormalUnhandled int64 = 0
	var showName = ""
	var deliveryEpisode []int64
	var deliveryEpisodeTotal []int64
	var waitDeliveryFrame int64
	//var has = false
	var taskType = []string{"todo", "step"}
	projectShowStepCategoryList.Data = append(projectShowStepCategoryList.Data, types.StepCategoryListConfigItem{
		Code:           "director",
		Color:          "",
		EntityID:       0,
		ID:             0,
		Index:          0,
		IsDeprecate:    "",
		IsEnableCensus: "",
		IsShow:         "",
		IsStandard:     "",
		ModuleCode:     "",
		Name:           "",
		ParentID:       0,
		ShowIndex:      0,
		ShowName:       "导演",
		TaskType:       "",
		UUID:           "",
	})

	for _, v := range projectShowStepCategoryList.Data {
		waitDone = 0
		waitDoneTotal = 0
		waitDelivery = 0
		waitDeliveryTotal = 0
		abnormalTotal = 0
		abnormalUnhandled = 0
		waitDeliveryFrame = 0
		categoryId := int64(v.ID)
		if v.ShowName == "" {
			showName = v.Name
		} else {
			showName = v.ShowName
		}

		if showName == "导演" {
			waitDone = directorTaskCount.Done
			waitDoneTotal = directorTaskCount.Total
		} else {
			for _, s := range taskType {

				if showName == "剧本" || showName == "分镜" || showName == "动捕" || showName == "成片" || showName == "导演" {
					// 只统计待办的数量
					if s != "todo" {
						continue
					}
				} else {
					// 默认统计工序任务的数量
					if s != "step" {
						continue
					}
				}

				k := fmt.Sprintf("%d-%s", v.ID, s)
				if _, ok := taskPlanDataCount[k]; ok {
					waitDone += taskPlanDataCount[k].Total - taskPlanDataCount[k].Done
					waitDoneTotal += taskPlanDataCount[k].Total
				}
				if _, ok := planDataCount[k]; ok {
					waitDelivery += planDataCount[k].Total - planDataCount[k].Done
					waitDeliveryTotal += planDataCount[k].Total
				}
			}
			//if _, ok := taskWorkOrderCount[categoryId]; ok {
			//	abnormalTotal = taskWorkOrderCount[categoryId].Total
			//	abnormalUnhandled = abnormalTotal - taskWorkOrderCount[categoryId].Done
			//}
		}

		deliveryEpisode = []int64{}
		deliveryEpisodeTotal = []int64{}

		// 关于特殊环节 交付数据从剧集环节封板配置来
		if showName == "剧本" || showName == "分镜" {
			waitDelivery = 0
			waitDeliveryTotal = 0
			if _, ok := episodeStepCategoryPlanSealTimeCount[categoryId]; ok {
				for _, episodeId := range episodeStepCategoryPlanSealTimeCount[categoryId].DeliveryEpisodeIdList {
					deliveryEpisode = append(deliveryEpisode, episodeId)
				}
				for _, episodeId := range episodeStepCategoryPlanSealTimeCount[categoryId].ShouldDeliveryEpisodeIdList {
					deliveryEpisodeTotal = append(deliveryEpisodeTotal, episodeId)
				}
			}

		} else if showName == "成片" {
			// 成片从剧集封板时间数据获取
			deliveryEpisode = episodePlanSealCount.Done
			deliveryEpisodeTotal = episodePlanSealCount.Plan
		} else if showName == "动画" {
			// 动画单独统计
			waitDeliveryTotal = shotMotionTaskCount.Plan
			waitDelivery = shotMotionTaskCount.Plan - shotMotionTaskCount.PlanDone
			waitDeliveryFrame = shotMotionTaskCount.PlanDoneFrame
		}

		if _, ok := resNameMap[showName]; ok {
			resNameMap[showName].StepCategoryIdList = append(resNameMap[showName].StepCategoryIdList, categoryId)

			resNameMap[showName].WaitDelivery += waitDelivery
			resNameMap[showName].WaitDeliveryTotal += waitDeliveryTotal
			resNameMap[showName].WaitDeliveryDeliveryFrame += waitDeliveryFrame

			resNameMap[showName].WaitDone += waitDone
			resNameMap[showName].WaitDoneTotal += waitDoneTotal

			resNameMap[showName].AbnormalTotal += abnormalTotal
			resNameMap[showName].AbnormalUnHandle += abnormalUnhandled
			if _, ok = moduleCodeMap[showName+v.ModuleCode]; !ok {
				resNameMap[showName].ModuleCodeList = append(resNameMap[showName].ModuleCodeList, v.ModuleCode)
				moduleCodeMap[showName+v.ModuleCode] = true
			}

			if len(deliveryEpisodeTotal) > 0 {
				for _, episodeId := range deliveryEpisodeTotal {
					if _, ok := resNameMap[showName].ShouldDeliveryEpisodeIdMap[episodeId]; ok {
						continue
					}
					resNameMap[showName].ShouldDeliveryEpisodeIdList = append(resNameMap[showName].ShouldDeliveryEpisodeIdList, episodeId)
					resNameMap[showName].ShouldDeliveryEpisodeIdMap[episodeId] = true
				}
				resNameMap[showName].WaitDeliveryTotal = int64(len(resNameMap[showName].ShouldDeliveryEpisodeIdList))
				resNameMap[showName].WaitDelivery = resNameMap[showName].WaitDeliveryTotal - int64(len(resNameMap[showName].DeliveryEpisodeIdList))
			}
			if len(deliveryEpisode) > 0 {
				for _, episodeId := range deliveryEpisode {
					if _, ok := resNameMap[showName].DeliveryEpisodeIdMap[episodeId]; ok {
						continue
					}
					resNameMap[showName].DeliveryEpisodeIdList = append(resNameMap[showName].DeliveryEpisodeIdList, episodeId)
					resNameMap[showName].DeliveryEpisodeIdMap[episodeId] = true
				}
				resNameMap[showName].WaitDelivery = resNameMap[showName].WaitDeliveryTotal - int64(len(resNameMap[showName].DeliveryEpisodeIdList))
			}
		} else {
			moduleCodeMap[showName+v.ModuleCode] = true
			res = append(res, types.StepCategoryTaskCountResItem{
				StepCategoryIdList: []int64{categoryId},
				ModuleCodeList:     []string{v.ModuleCode},
				StepCategoryName:   showName,

				WaitDelivery:              waitDelivery,
				WaitDeliveryTotal:         waitDeliveryTotal,
				WaitDeliveryDeliveryFrame: waitDeliveryFrame,

				WaitDone:      waitDone,
				WaitDoneTotal: waitDoneTotal,

				AbnormalTotal:    abnormalTotal,
				AbnormalUnHandle: abnormalUnhandled,

				DeliveryEpisodeIdMap:       make(map[int64]bool),
				ShouldDeliveryEpisodeIdMap: make(map[int64]bool),
				Unit:                       "个",

				AbnormalUnHandleTaskIdMap: make(map[int64]bool),
				AbnormalTotalTaskIdMap:    make(map[int64]bool),
			})
			resNameMap[showName] = &res[resIndex]
			if len(deliveryEpisodeTotal) > 0 {
				resNameMap[showName].ShouldDeliveryEpisodeIdList = deliveryEpisodeTotal
				for _, episodeId := range deliveryEpisodeTotal {
					resNameMap[showName].ShouldDeliveryEpisodeIdMap[episodeId] = true
				}
				resNameMap[showName].WaitDeliveryTotal = int64(len(resNameMap[showName].ShouldDeliveryEpisodeIdList))
				resNameMap[showName].WaitDelivery = resNameMap[showName].WaitDeliveryTotal
			}
			if len(deliveryEpisode) > 0 {
				resNameMap[showName].DeliveryEpisodeIdList = deliveryEpisode
				for _, episodeId := range deliveryEpisode {
					resNameMap[showName].DeliveryEpisodeIdMap[episodeId] = true
				}
				resNameMap[showName].WaitDelivery = resNameMap[showName].WaitDeliveryTotal - int64(len(resNameMap[showName].DeliveryEpisodeIdList))
			}
			resIndex += 1
		}

		if categoryId > 0 {
			if _, ok := abnormalStepCategoryTaskList[categoryId]; ok {
				for _, taskId := range abnormalStepCategoryTaskList[categoryId].AbnormalTotalTaskId {
					if _, ok = resNameMap[showName].AbnormalTotalTaskIdMap[taskId]; !ok {
						resNameMap[showName].AbnormalTotalTaskIdMap[taskId] = true
						resNameMap[showName].AbnormalTotal += 1
					}
				}
				for _, taskId := range abnormalStepCategoryTaskList[categoryId].AbnormalUnHandleTaskId {
					if _, ok = resNameMap[showName].AbnormalUnHandleTaskIdMap[taskId]; !ok {
						resNameMap[showName].AbnormalUnHandleTaskIdMap[taskId] = true
						resNameMap[showName].AbnormalUnHandle += 1
					}
				}
			}

			if showName == "分镜" {
				println(resNameMap[showName].AbnormalTotal, resNameMap[showName].AbnormalUnHandle)
			}
		}

	}

	return
}

// getTaskPlanDepartmentCount 查询计划完成任务 部门统计
func (w *Workbench) getTaskPlanDepartmentCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader, departmentIdList []int64, departmentUserIdList []int64) (res map[int64]*types.GetTaskPlanDepartmentCount, err error) {
	var qresTotal []types.GetTaskPlanDepartmentCountItem
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_member on task_member.task_id = task.id join user on user.id = task_member.user_id join tenant_user on tenant_user.user_id = user.union_id").
		Select("count(distinct task.id) count,task.step_type step_type,tenant_user.department_id as id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("tenant_user.department_id IN ? and task.step_type <>'step_category'", departmentIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.plan_end_time BETWEEN ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("tenant_user.department_id,task.step_type").Find(&qresTotal).Error
	if err != nil {
		return
	}
	res = make(map[int64]*types.GetTaskPlanDepartmentCount)
	var resList []types.GetTaskPlanDepartmentCount
	resIndex := 0
	for _, item := range qresTotal {
		if _, ok := res[item.Id]; !ok {
			resList = append(resList, types.GetTaskPlanDepartmentCount{
				Id: item.Id,
			})

			res[item.Id] = &resList[resIndex]
			resIndex += 1
		}
		res[item.Id].Total += item.Count
		switch item.StepType {
		case "step", "standard_make_to_do":
			res[item.Id].TotalStep += item.Count
		case "standard_meeting", "standard_review":
			res[item.Id].TotalStandardTodo += item.Count
		default:
			res[item.Id].TotalOther += item.Count
		}
	}

	var qresDone []types.GetTaskPlanDepartmentCountItem

	dbr = ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_member on task_member.task_id = task.id join user on user.id = task_member.user_id join tenant_user on tenant_user.user_id = user.union_id").
		Select("count(id) count,task.step_type step_type,tenant_user.department_id as id").
		Where("task.tenant_id = ? and simpleJSONExtractInt(task.json, 'task_status') = 7 ", userInfo.TenantId).
		Where("tenant_user.department_id IN ? and task.step_type <>'step_category'", departmentIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.plan_end_time BETWEEN ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("tenant_user.department_id,task.step_type").Find(&qresDone).Error

	for _, item := range qresDone {
		if _, ok := res[item.Id]; !ok {
			resList = append(resList, types.GetTaskPlanDepartmentCount{
				Id: item.Id,
			})
			res[item.Id] = &resList[resIndex]
			resIndex += 1
		}
		res[item.Id].Done += item.Count
		switch item.StepType {
		case "step", "standard_make_to_do":
			res[item.Id].DoneStep += item.Count
		case "standard_meeting", "standard_review":
			res[item.Id].DoneStandardTodo += item.Count
		default:
			res[item.Id].DoneOther += item.Count
		}
	}

	return
}

// getPlanDepartmentCount 查询计划完成任务 部门统计
func (w *Workbench) getPlanDepartmentCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader, departmentIdList []int64, pqRes map[int64][]int64) (res map[int64]*types.TaskCountQueryCount, err error) {
	epStepCategoryWhere := ClickHouse.DB
	for scid, episodeIdList := range pqRes {
		epStepCategoryWhere = epStepCategoryWhere.Or("r_step_category.link_id = ? and task.episode_id in ?", scid, episodeIdList)
	}

	var qTotalRes []types.GetPlanDepartmentCount
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Joins("join task_member on task_member.task_id = task.id join user on user.id = task_member.user_id join tenant_user on tenant_user.user_id = user.union_id").
		Select("count(distinct task.id) number,tenant_user.department_id as id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type = 'step'").
		Where("tenant_user.department_id IN ?", departmentIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where(epStepCategoryWhere)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("tenant_user.department_id").Find(&qTotalRes).Error

	if err != nil {
		return
	}
	var resList []types.TaskCountQueryCount
	var resIndex = 0
	res = make(map[int64]*types.TaskCountQueryCount)
	for _, re := range qTotalRes {
		if _, ok := res[re.Id]; !ok {
			resList = append(resList, types.TaskCountQueryCount{
				Id: re.Id,
			})
			res[re.Id] = &resList[resIndex]
			resIndex += 1
		}
		res[re.Id].Total = re.Number
	}

	var qDoneRes []types.GetPlanDepartmentCount
	dbr = ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Joins("join task_member on task_member.task_id = task.id join user on user.id = task_member.user_id join tenant_user on tenant_user.user_id = user.union_id").
		Select("count(distinct task.id) Number,tenant_user.department_id as id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type = 'step' and simpleJSONExtractInt(task.json, 'task_status') = 7").
		Where("tenant_user.department_id IN ?", departmentIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where(epStepCategoryWhere)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("tenant_user.department_id").Find(&qDoneRes).Error

	if err != nil {
		return
	}

	for _, re := range qDoneRes {
		if _, ok := res[re.Id]; !ok {
			resList = append(resList, types.TaskCountQueryCount{
				Id: re.Id,
			})
			res[re.Id] = &resList[resIndex]
			resIndex += 1
		}

		res[re.Id].Done = re.Number
	}

	return
}

// 查询环节工单处理数据统计
func (w *Workbench) getStepCategoryTaskWorkOrderCount(req types.StepCategoryTaskCountReq) (res map[int64]types.TaskCountQueryCount, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("workbench:getStepCategoryTaskWorkOrderCount-%s", teamonesHelper.GenerateMd5(string(reqStr)))

	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return res, err
		}
	}
	var qr []types.TaskCountQueryCount
	err = ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Where("task_work_order.project_id = ?", req.ProjectId).
		Where("task_work_order.created between ? and ?", req.StartTime, req.EndTime).
		Select("task_work_order.step_category_id as id,count(task_work_order.status <> 2 or null) as total,count(task_work_order.status = 1 or null) done").
		Group("task_work_order.step_category_id").
		Find(&qr).Error
	res = make(map[int64]types.TaskCountQueryCount)
	for _, count := range qr {
		res[count.Id] = count
	}

	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return
}

// 查询导演任务完成情况
func (w *Workbench) getDirectorTaskCount(projectId int64, startTime, endTime int64, planDoneWanted bool, actualDoneWanted bool) (res types.TaskCountDataItem, err error) {
	var reqStruct = map[string]interface{}{"project_id": projectId, "start_time": startTime, "end_time": endTime, "plan_done_wanted": planDoneWanted, "actual_done_wanted": actualDoneWanted}
	reqStr, _ := json.Marshal(reqStruct)
	rk := fmt.Sprintf("workbench:getDirectorTaskCount-%s", reqStr)
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return
		}
		err = nil
	}

	var directorIdList []int64

	err = ClickHouse.DB.Model(&model.ProjectMember{}).Where("role_code = 'director'").
		Where("project_id = ?", projectId).Select("distinct user_id").Find(&directorIdList).Error
	if err != nil || len(directorIdList) == 0 {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN task_member on task_member.task_id = task.id").
		Select("count(distinct task.id) Total").
		Where("task.step_type not in ('step_category', 'step') and task_member.user_id in ?", directorIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	dbr = dbr.Where("task.plan_end_time BETWEEN ? and ?", startTime, endTime)

	dbr.Where("task.project_id = ?", projectId)

	err = dbr.Pluck("Total", &res.Total).Error
	if err != nil {
		return
	}

	if planDoneWanted {
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN task_member on task_member.task_id = task.id").
			Select("count(distinct task.id) PlanDone").
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7").
			Where("task.step_type not in ('step_category', 'step') and task_member.user_id in ?", directorIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.plan_end_time BETWEEN ? and ?", startTime, endTime).
			Where("task.project_id = ?", projectId).
			Pluck("PlanDone", &res.Done).Error
		if err != nil {
			return
		}
	}

	if actualDoneWanted {
		err = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN task_member on task_member.task_id = task.id").
			Select("count(distinct task.id) ActualDone").
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7").
			Where("task.step_type not in ('step_category', 'step') and task_member.user_id in ?", directorIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.end_time BETWEEN ? and ?", startTime, endTime).
			Where("task.project_id = ?", projectId).
			Pluck("ActualDone", &res.ActualDone).Error
		if err != nil {
			return
		}
	}

	// 异常 逾期未完成
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN task_member on task_member.task_id = task.id").
		Select("count(distinct task.id) Abnormal").
		Where("simpleJSONExtractInt(task.json, 'task_status') <> 7 and task.plan_end_time < ?", time.Now().Unix()).
		Where("task.step_type not in ('step_category', 'step') and task_member.user_id in ?", directorIdList).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.plan_end_time BETWEEN ? and ?", startTime, endTime).Where("task.project_id = ?", projectId).
		Pluck("Abnormal", &res.Abnormal).Error
	if err != nil {
		return
	}
	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return
}

// 查询剧集环节封板时间配置统计
func (w *Workbench) getEpisodeStepCategoryPlanSealTimeCount(req types.StepCategoryTaskCountReq, stepCategoryIdList []int64) (res map[int64]types.EpisodePlanSealTimeCount, err error) {

	var reqStruct = map[string]interface{}{"req": req, "step_category_id": stepCategoryIdList}
	reqStr, _ := json.Marshal(reqStruct)
	rk := fmt.Sprintf("workbench:GetPlanDoneEpisodeStepCategory-%s", teamonesHelper.GenerateMd5(string(reqStr)))
	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return res, err
		}
	}
	var resList []types.EpisodePlanSealTimeCount
	var resMapIndex = make(map[int64]int)
	resMapIndexCurrent := 0
	pqRes, err := w.GetPlanDoneEpisodeStepCategory(req.ProjectId, req.StartTime, req.EndTime, stepCategoryIdList)
	if err != nil || len(pqRes) == 0 {
		return
	}
	pwhere := ClickHouse.DB

	for _, re := range pqRes {
		if _, ok := resMapIndex[re.StepCategoryId]; ok {
			resList[resMapIndex[re.StepCategoryId]].ShouldDeliveryEpisodeIdList = append(resList[resMapIndex[re.StepCategoryId]].ShouldDeliveryEpisodeIdList, re.EpisodeId)
		} else {
			resList = append(resList, types.EpisodePlanSealTimeCount{
				ShouldDeliveryEpisodeIdList: []int64{re.EpisodeId},
				DeliveryEpisodeIdList:       []int64{},
				StepCategoryId:              re.StepCategoryId,
			})
			resMapIndex[re.StepCategoryId] = resMapIndexCurrent
			resMapIndexCurrent += 1
		}
		pwhere = pwhere.Or("es.entity_id = ? and es.step_category_id = ?", re.EpisodeId, re.StepCategoryId)
	}

	q := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Where("project_id = ?", req.ProjectId).
		Select("toInt64(arrayJoin(splitByChar(',',step_category_ids))) as step_category_id,*")

	var qr []types.EpisodePlanSealTimeCountItem
	err = ClickHouse.DB.Table("(?) as es", q).
		Joins("join entity on entity.id = es.entity_id").
		Where(pwhere).
		Where("es.seal_time <= ?", time.Now().Unix()).
		Select("es.step_category_id step_category_id,es.entity_id as episode_id").
		Find(&qr).Error

	res = make(map[int64]types.EpisodePlanSealTimeCount)
	for _, item := range qr {
		if _, ok := resMapIndex[item.StepCategoryId]; ok {
			resList[resMapIndex[item.StepCategoryId]].DeliveryEpisodeIdList = append(resList[resMapIndex[item.StepCategoryId]].DeliveryEpisodeIdList, item.EpisodeId)
		} else {
			resList = append(resList, types.EpisodePlanSealTimeCount{
				ShouldDeliveryEpisodeIdList: []int64{},
				DeliveryEpisodeIdList:       []int64{item.EpisodeId},
				StepCategoryId:              item.StepCategoryId,
			})
			resMapIndex[item.StepCategoryId] = resMapIndexCurrent
			resMapIndexCurrent += 1
		}
	}

	for i, count := range resList {
		res[count.StepCategoryId] = resList[i]
	}
	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return
}

// 查询剧集封板情况
func (w *Workbench) getEpisodePlanSealCount(projectId, startTime, endTime int64) (res types.EpisodePlanSealCountRes, err error) {

	rk := fmt.Sprintf("workbench:%d:getEpisodePlanSealCount-%d-%d", projectId, startTime, endTime)

	val, _ := ClickHouse.Rdb.Get(rk).Result()
	if val != "" {
		err = json.Unmarshal([]byte(val), &res)
		if err == nil {
			return res, err
		}
	}

	var episodeSealStatusList []types.EpisodeJson
	err = ClickHouse.DB.Model(&model.Entity{}).
		Where("module_id = (select id from module where code = 'episode' limit 1)").
		Where("project_id = ? and simpleJSONExtractInt(json, 'episode_plan_seal_time') between ? and ?", projectId, startTime, endTime).
		Select("id,simpleJSONExtractInt(json, 'episode_seal_time') episode_seal_time").Find(&episodeSealStatusList).
		Error
	if err != nil {
		return
	}
	nowUnix := time.Now().Unix()
	for _, ej := range episodeSealStatusList {
		if ej.EpisodeSealTime <= nowUnix && ej.EpisodeSealTime > 0 {
			res.Done = append(res.Done, int64(ej.Id))
		}
		res.Plan = append(res.Plan, int64(ej.Id))
	}
	rs, err := json.Marshal(res)
	if err != nil {
		return
	}
	_, err = ClickHouse.Rdb.Set(rk, rs, time.Second*60).Result()
	if err != nil {
		return
	}
	return
}

// 查询剧集实际封板情况
func (w *Workbench) getEpisodeActualSealCount(projectId int64, startTime, endTime int64) (res []int64, err error) {

	var episodeSealStatusList []types.EpisodeJson
	err = ClickHouse.DB.Model(&model.Entity{}).
		Where("module_id = (select id from module where code = 'episode' limit 1)").
		Where("project_id = ? and simpleJSONExtractInt(json, 'episode_seal_time') between ? and ?", projectId, startTime, endTime).
		Select("id,simpleJSONExtractInt(json, 'episode_seal_time') episode_seal_time").Find(&episodeSealStatusList).
		Error
	if err != nil {
		return
	}
	nowUnix := time.Now().Unix()
	for _, ej := range episodeSealStatusList {
		if ej.EpisodeSealTime <= nowUnix && ej.EpisodeSealTime > 0 {
			res = append(res, int64(ej.Id))
		}
	}

	return
}

// 查询所有环节异常数据
func (w *Workbench) getAbnormalTaskCount(req types.StepCategoryTaskCountReq, pList map[int64][]int64) (res map[int64]types.GetAbnormalTaskCountResItem, err error) {
	res = make(map[int64]types.GetAbnormalTaskCountResItem)

	var resList []types.GetAbnormalTaskCountResItem
	var abNormalTaskList []types.GetAbnormalTaskCountItem
	var pWhere = ClickHouse.DB
	now := time.Now().Unix()
	overdueWhere := ClickHouse.DB.Where("task.plan_end_time > 0 and simpleJSONExtractInt(task.json, 'task_status') = 7 and task.end_time > task.plan_end_time").
		Or("simpleJSONExtractInt(task.json, 'task_status') <> 7 and task.plan_end_time < ?", now)
	var workOrderWhere = ClickHouse.DB.Where("task_work_order.id > 0")
	pi := 0

	for stepCategoryId, episodeIdList := range pList {
		pWhere = pWhere.Or("r_step_category.link_id = ? and r_episode.link_id in ?", stepCategoryId, episodeIdList)
		pi += 1
	}
	tWhere := ClickHouse.DB.Where("task.plan_end_time between ? and ?", req.StartTime, req.EndTime)
	if pi > 0 {
		tWhere = tWhere.Or(pWhere)
	}

	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Joins("LEFT JOIN task_work_order on task_work_order.task_id = task.id").
		Where("task.project_id = ? and task.step_type not in ('step_category')", req.ProjectId).
		Where(tWhere).
		Where(ClickHouse.DB.Where(overdueWhere).Or(workOrderWhere)).
		Select("distinct r_step_category.link_id step_category_id,task.id task_id").
		Find(&abNormalTaskList).Error

	if err != nil {
		return
	}
	resListIndex := 0
	resMap := make(map[int64]int)
	for _, item := range abNormalTaskList {
		if _, ok := resMap[item.StepCategoryId]; !ok {
			resList = append(resList, types.GetAbnormalTaskCountResItem{
				StepCategoryId:         item.StepCategoryId,
				AbnormalTotalTaskId:    []int64{item.TaskId},
				AbnormalUnHandleTaskId: []int64{},
			})
			resMap[item.StepCategoryId] = resListIndex
			resListIndex += 1
		} else {
			resList[resMap[item.StepCategoryId]].AbnormalTotalTaskId = append(resList[resMap[item.StepCategoryId]].AbnormalTotalTaskId, item.TaskId)
		}
	}

	var abNormalUnHandleTaskList []types.GetAbnormalTaskCountItem

	overdueUnHandleWhere := ClickHouse.DB.Or("simpleJSONExtractInt(task.json, 'task_status') <> 7 and task.plan_end_time > 0 and task.plan_end_time < ?", now)
	var workOrderUnHandleWhere = ClickHouse.DB.Where("task_work_order.id > 0 and task_work_order.status in (0,3)")
	err = ClickHouse.DB.Model(&model.Task{}).
		Joins("join (select * from task_relation_tag where link_module_code = 'episode') r_episode on r_episode.task_id = task.id").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id").
		Joins("LEFT JOIN task_work_order on task_work_order.task_id = task.id").
		Where("task.project_id = ? and task.step_type not in ('step_category')", req.ProjectId).
		Where(tWhere).
		Where(ClickHouse.DB.Where(overdueUnHandleWhere).Or(workOrderUnHandleWhere)).
		Select("distinct r_step_category.link_id step_category_id,task.id task_id").
		Find(&abNormalUnHandleTaskList).Error
	if err != nil {
		return
	}
	for _, item := range abNormalUnHandleTaskList {
		if _, ok := resMap[item.StepCategoryId]; !ok {
			resList = append(resList, types.GetAbnormalTaskCountResItem{
				StepCategoryId:         item.StepCategoryId,
				AbnormalTotalTaskId:    []int64{},
				AbnormalUnHandleTaskId: []int64{item.TaskId},
			})
			resMap[item.StepCategoryId] = resListIndex
			resListIndex += 1
		} else {
			resList[resMap[item.StepCategoryId]].AbnormalUnHandleTaskId = append(resList[resMap[item.StepCategoryId]].AbnormalUnHandleTaskId, item.TaskId)
		}
	}
	for i, item := range resList {
		res[item.StepCategoryId] = resList[i]
	}

	return
}

// GetAbnormalDepartmentCount 获取异常部门统计
func (w *Workbench) GetAbnormalDepartmentCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader, departmentIdList []int64) (res map[int64]types.GetAbnormalDepartmentCountRes, err error) {
	//新增任务 task created time
	var addTaskRes []types.GetPlanDepartmentCount
	addedTaskDb := ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_member on task_member.task_id = task.id join user on user.id = task_member.user_id join tenant_user on tenant_user.user_id = user.union_id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type = 'step'").
		Where("tenant_user.department_id IN ?", departmentIdList).
		Where("JSONExtractString(task.json,'task_is_append') ='yes' and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.created BETWEEN ? AND ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		addedTaskDb = addedTaskDb.Where("task.project_id = ?", req.ProjectId)
	}

	err = addedTaskDb.
		Select("count(distinct task.id) number,tenant_user.department_id as id").
		Group("tenant_user.department_id").
		Find(&addTaskRes).Error

	if err != nil {
		return
	}

	//未处理的工单 task_work_order 截至时间 task_work_order->task_work_order_user->user->tenant_user[department_id]

	statusNotClosedGroup := [...]int64{
		model.TaskWorkOrderNotProcessed,
		model.TaskWorkOrderProgress,
	}

	var waitHandleWorkOrderRes []types.GetPlanDepartmentCount
	waitHandleWorkOrderDb := ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Joins("join task_work_order_user on task_work_order.id = task_work_order_user.work_order_id join user on user.id = task_work_order_user.user_id join tenant_user on tenant_user.user_id = user.union_id")

	if req.ProjectId > 0 {
		waitHandleWorkOrderDb = waitHandleWorkOrderDb.Where("task_work_order.project_id = ?", req.ProjectId)
	}

	err = waitHandleWorkOrderDb.Where("task_work_order.tenant_id = ?", userInfo.TenantId).
		Where("tenant_user.department_id IN ?", departmentIdList).
		Where("task_work_order.status in ?", statusNotClosedGroup).
		Where("task_work_order.deadline_time BETWEEN ? AND ?", req.StartTime, req.EndTime).
		Select("count(distinct task_work_order.id) number,tenant_user.department_id as id").
		Group("tenant_user.department_id").
		Find(&waitHandleWorkOrderRes).
		Error

	if err != nil {
		return
	}

	//延期任务 review_request created,created_by ->user->tenant_user[department_id]
	module := model.Module{}
	taskModule, err := module.GetByCode("task")
	if err != nil {
		return
	}

	var reviewRequestRes []types.GetPlanDepartmentCount
	reviewRequestDb := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("join user on review_request.created_by = user.id join tenant_user on tenant_user.user_id = user.union_id join task on review_request.link_id = task.id")
	if req.ProjectId > 0 {
		reviewRequestDb = reviewRequestDb.Where("task.project_id = ?", req.ProjectId)
	}

	err = reviewRequestDb.
		Where("review_request.created BETWEEN ? AND ?", req.StartTime, req.EndTime).
		Where("review_request.module_id=?", taskModule.Id).
		Where("tenant_user.department_id IN ?", departmentIdList).
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Select("count(distinct task.id) number,tenant_user.department_id as id").
		Group("tenant_user.department_id").
		Find(&reviewRequestRes).
		Error
	if err != nil {
		return
	}

	addTaskMap := map[int64]int64{}
	for _, addTaskResItem := range addTaskRes {
		addTaskMap[addTaskResItem.Id] = addTaskResItem.Number
	}

	waitHandleWorkOrderMap := map[int64]int64{}
	for _, waitHandleWorkOrderResItem := range waitHandleWorkOrderRes {
		waitHandleWorkOrderMap[waitHandleWorkOrderResItem.Id] = waitHandleWorkOrderResItem.Number
	}

	reviewRequestMap := map[int64]int64{}
	for _, reviewRequestItem := range reviewRequestRes {
		reviewRequestMap[reviewRequestItem.Id] = reviewRequestItem.Number
	}

	res = map[int64]types.GetAbnormalDepartmentCountRes{}
	for _, departmentIdItem := range departmentIdList {
		res[departmentIdItem] = types.GetAbnormalDepartmentCountRes{
			AbnormalAddedTask:               addTaskMap[departmentIdItem],
			AbnormalWaitHandleTaskWorkOrder: waitHandleWorkOrderMap[departmentIdItem],
			AbnormalDelayTask:               reviewRequestMap[departmentIdItem],
		}
	}

	return
}

// GroupAbnormalAddCount 部门异常任务情况
func (w *Workbench) GroupAbnormalAddCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader) (res int64, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:GroupAbnormalAddCount:%d%d%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()
	var userIdList []int64
	if req.DepartmentId > 0 {
		err = ClickHouse.DB.Model(&model.TenantUser{}).
			Joins("join user on user.union_id = tenant_user.user_id").
			Where("tenant_user.department_id = ?", req.DepartmentId).
			Select("distinct user.id uid").Pluck("uid", &userIdList).Error
		if err != nil || len(userIdList) == 0 {
			return res, err
		}
	}
	if len(req.TodoStepCategoryIdList) == 0 && len(userIdList) == 0 {
		return
	}

	var rStepCategoryJoined = false
	addedTaskDb := ClickHouse.DB.Model(&model.Task{}).
		Joins("left join task_member on task_member.task_id = task.id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type <> 'step_category'").
		Where("JSONExtractString(task.json,'task_is_append') ='yes' ").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.created BETWEEN ? AND ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		addedTaskDb.Where("task.project_id = ?", req.ProjectId)
	}
	if len(req.TodoStepCategoryIdList) > 0 {
		if !rStepCategoryJoined {
			addedTaskDb.Joins("JOIN task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')")
		}
		addedTaskDb.Where("r_step_category.link_id in ?", req.TodoStepCategoryIdList)
	}
	if len(userIdList) > 0 {
		addedTaskDb.Where("task_member.user_id in ?", userIdList)
	}

	err = addedTaskDb.
		Distinct("task.id").
		Count(&res).Error

	if err != nil {
		return res, err
	}
	return
}

// GroupAbnormalWorkOrderCount 未处理工单异常查询
func (w *Workbench) GroupAbnormalWorkOrderCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader) (res int64, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:GroupAbnormalWorkOrderCount:%d%d%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()
	var userIdList []int64

	if len(req.TodoStepCategoryIdList) == 0 && req.DepartmentId == 0 {
		return
	}

	workOrderTaskDb := ClickHouse.DB.Model(&model.Task{}).
		Where("task.step_type = ?", "work_order_to_do")

	if req.ProjectId > 0 {
		workOrderTaskDb.Where("task.project_id = ?", req.ProjectId)
	}
	if len(req.TodoStepCategoryIdList) > 0 {
		workOrderTaskDb.
			Joins("JOIN task_relation_tag r_step_category "+
				"on r_step_category.task_id = task.id "+
				"and r_step_category.link_module_code in ('step_category','show_step_category')").
			Where("r_step_category.link_id in ?", req.TodoStepCategoryIdList)
	}

	if len(userIdList) > 0 {
		workOrderTaskDb.
			Joins("join task_member on task_member.task_id = task.id").
			Where("task_member.partner in ?", userIdList)
	}

	if req.DepartmentId > 0 {
		workOrderTaskDb.
			Joins("join task_member on task_member.task_id = task.id join task_member_relation on task_member_relation.task_member_id = task_member.id").
			Where("task_member_relation.department_id = ? and task_member.partner > 0", req.DepartmentId)
	}

	err = workOrderTaskDb.
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("simpleJSONExtractInt(task.json,'task_status') not in (3,7)").
		Where("task.plan_end_time BETWEEN ? AND ?", req.StartTime, req.EndTime).
		Distinct("task.id").
		Count(&res).
		Error

	if err != nil {
		return res, err
	}

	return
}

// GroupAbnormalDelayTaskCount 延期任务数
func (w *Workbench) GroupAbnormalDelayTaskCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader) (res int64, err error) {

	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:GroupAbnormalDelayTaskCount:%d%d%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()
	var userIdList []int64
	if req.DepartmentId > 0 {
		err = ClickHouse.DB.Model(&model.TenantUser{}).
			Joins("join user on user.union_id = tenant_user.user_id").
			Where("tenant_user.department_id = ?", req.DepartmentId).
			Select("distinct user.id uid").Pluck("uid", &userIdList).Error
		if err != nil || len(userIdList) == 0 {
			return res, err
		}
	}
	if len(req.TodoStepCategoryIdList) == 0 && len(userIdList) == 0 {
		return
	}
	//延期任务 review_request created,created_by ->user->tenant_user[department_id]
	module := model.Module{}
	taskModule, err := module.GetByCode("task")
	if err != nil {
		return res, err
	}

	var rStepCategoryJoined = false
	reviewRequestDb := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("join task on review_request.link_id = task.id").
		Joins("left join task_member on task_member.task_id = task.id").
		Where("review_request.created BETWEEN ? AND ?", req.StartTime, req.EndTime).
		Where("review_request.module_id = ?", taskModule.Id).
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.ProjectId > 0 {
		reviewRequestDb.Where("task.project_id = ?", req.ProjectId)
	}
	if len(req.TodoStepCategoryIdList) > 0 {
		if !rStepCategoryJoined {
			reviewRequestDb.Joins("JOIN task_relation_tag r_step_category on r_step_category.task_id = review_request.link_id and r_step_category.link_module_code in ('step_category','show_step_category')")
		}
		reviewRequestDb.Where("r_step_category.link_id in ?", req.TodoStepCategoryIdList)
	}

	if len(userIdList) > 0 {
		reviewRequestDb.Where("task_member.user_id in ?", userIdList)
	}

	err = reviewRequestDb.
		Distinct("review_request.id").
		Count(&res).
		Error
	if err != nil {
		return res, err
	}
	return
}

// GetAbnormalDepartmentCountMap 获取异常部门统计
func (w *Workbench) GetAbnormalDepartmentCountMap(req types.GroupTaskCountReq, typeAbnormal string, userInfo types.UserInfoHeader, departmentIdList []int64) (res map[int64]int64, err error) {
	//新增任务 task created time
	switch typeAbnormal {
	case "added_task":
		var addTaskRes []types.GetPlanDepartmentCount
		addedTaskDb := ClickHouse.DB.Model(&model.Task{}).
			Joins("join task_member on task_member.task_id = task.id join user on user.id = task_member.user_id join tenant_user on tenant_user.user_id = user.union_id").
			Where("task.tenant_id = ?", userInfo.TenantId).
			Where("task.step_type = 'step'").
			Where("tenant_user.department_id IN ?", departmentIdList).
			Where("JSONExtractString(task.json,'task_is_append') ='yes' and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.created BETWEEN ? AND ?", req.StartTime, req.EndTime)

		if req.ProjectId > 0 {
			addedTaskDb = addedTaskDb.Where("task.project_id = ?", req.ProjectId)
		}

		err = addedTaskDb.
			Select("count(distinct task.id) number,tenant_user.department_id as id").
			Group("tenant_user.department_id").
			Find(&addTaskRes).Error

		if err != nil {
			return
		}

		res = map[int64]int64{}
		for _, addTaskResItem := range addTaskRes {
			res[addTaskResItem.Id] = addTaskResItem.Number
		}

	case "wait_handle_work_order":
		//未处理的工单 task_work_order 截至时间 task_work_order->task_work_order_user->user->tenant_user[department_id]

		statusNotClosedGroup := [...]int64{
			model.TaskWorkOrderNotProcessed,
			model.TaskWorkOrderProgress,
		}

		var waitHandleWorkOrderRes []types.GetPlanDepartmentCount
		waitHandleWorkOrderDb := ClickHouse.DB.Model(&model.TaskWorkOrder{}).
			Joins("join task_work_order_user on task_work_order.id = task_work_order_user.work_order_id join user on user.id = task_work_order_user.user_id join tenant_user on tenant_user.user_id = user.union_id")

		if req.ProjectId > 0 {
			waitHandleWorkOrderDb = waitHandleWorkOrderDb.Where("task_work_order.project_id = ?", req.ProjectId)
		}

		err = waitHandleWorkOrderDb.Where("task_work_order.tenant_id = ?", userInfo.TenantId).
			Where("tenant_user.department_id IN ?", departmentIdList).
			Where("task_work_order.status in ?", statusNotClosedGroup).
			Where("task_work_order.deadline_time BETWEEN ? AND ?", req.StartTime, req.EndTime).
			Select("count(distinct task_work_order.id) number,tenant_user.department_id as id").
			Group("tenant_user.department_id").
			Find(&waitHandleWorkOrderRes).
			Error

		if err != nil {
			return
		}

		res = map[int64]int64{}
		for _, waitHandleWorkOrderResItem := range waitHandleWorkOrderRes {
			res[waitHandleWorkOrderResItem.Id] = waitHandleWorkOrderResItem.Number
		}

	case "delay_task":
		//延期任务 review_request created,created_by ->user->tenant_user[department_id]
		module := model.Module{}
		taskModule, err := module.GetByCode("task")
		if err != nil {
			return res, err
		}

		var reviewRequestRes []types.GetPlanDepartmentCount
		reviewRequestDb := ClickHouse.DB.Model(&model.ReviewRequest{}).
			Joins("join user on review_request.created_by = user.id join tenant_user on tenant_user.user_id = user.union_id join task on review_request.link_id = task.id")
		if req.ProjectId > 0 {
			reviewRequestDb = reviewRequestDb.Where("task.project_id = ?", req.ProjectId)
		}

		err = reviewRequestDb.
			Where("review_request.created BETWEEN ? AND ?", req.StartTime, req.EndTime).
			Where("review_request.module_id=?", taskModule.Id).
			Where("tenant_user.department_id IN ?", departmentIdList).
			Where("task.tenant_id = ?", userInfo.TenantId).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Select("count(distinct review_request.id) number,tenant_user.department_id as id").
			Group("tenant_user.department_id").
			Find(&reviewRequestRes).
			Error
		if err != nil {
			return res, err
		}

		res = map[int64]int64{}
		for _, reviewRequestItem := range reviewRequestRes {
			res[reviewRequestItem.Id] = reviewRequestItem.Number
		}
	}
	return
}

// GroupUpstreamDeliveryCount 部门完成交付情况
func (w *Workbench) GroupUpstreamDeliveryCount(ctx *gin.Context, req types.GroupTaskCountReq, userInfo types.UserInfoHeader) (res []types.UpstreamDeliveryDetail, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:GroupUpstreamDeliveryCount:%d%d%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil

	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()

	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}

	projectShowStepCategoryMap := make(map[int64]string)
	for _, datum := range projectShowStepCategoryList.Data {
		if datum.ShowName == "" {
			projectShowStepCategoryMap[int64(datum.ID)] = datum.Name
		} else {
			projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName
		}
	}

	if req.DepartmentId > 0 {
		var DepartmentService = Department{}

		department, err := DepartmentService.GetDepartment(ClickHouse.DB.Where("id = ?", req.DepartmentId))
		if err != nil {
			return res, err
		}

		if department.StepCategoryIds == "" {
			return res, err
		}
		var existCheck = make(map[int64]bool)
		for _, s := range strings.Split(department.StepCategoryIds, ",") {
			si := IntVal(s)
			if si <= 0 {
				continue
			}

			if _, ok := existCheck[si]; !ok {
				req.TodoStepCategoryIdList = append(req.TodoStepCategoryIdList, si)
				existCheck[si] = true
			}
		}
	}

	if len(req.TodoStepCategoryIdList) == 0 {
		return
	}
	var stepCategoryUpstreamDownstreamRelation map[int64]types.GetStepCategoryUpstreamDownstreamRelationItem

	var stepCategoryService StepCategory
	stepCategoryUpstreamDownstreamRelation, err = stepCategoryService.GetStepCategoryUpstreamDownstreamRelation(ctx, req.TodoStepCategoryIdList)
	if err != nil {
		return
	}

	var upstreamStepCategoryIdList []int64
	var upstreamStepCategoryMap = make(map[int64]bool)
	for _, item := range stepCategoryUpstreamDownstreamRelation {
		for _, upstreamStepCategoryId := range item.Upstream {
			if _, ok := upstreamStepCategoryMap[upstreamStepCategoryId]; ok {
				continue
			}
			upstreamStepCategoryMap[upstreamStepCategoryId] = true
			upstreamStepCategoryIdList = append(upstreamStepCategoryIdList, upstreamStepCategoryId)
		}
	}

	if len(upstreamStepCategoryIdList) == 0 {
		return
	}

	// 查询交付情况
	var upstreamDeliveryActuallyCount = make(map[int64]int64) // 实际交付
	var upstreamDeliveryCount = make(map[int64]int64)         //交付
	var upstreamDeliveryTotalCount = make(map[int64]int64)    //计划交付
	var dbr = ClickHouse.DB

	// 计划交付总数
	var qTotalRes []types.GetPlanDepartmentCount
	dbr = ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Joins("join task_relation_tag r_episode on r_episode.task_id = task.id and r_episode.link_module_code in ('episode')").
		Select("count(distinct task.id) Number,r_step_category.link_id as Id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type in ('step', 'standard_make_to_do')").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("r_step_category.link_id in ?", upstreamStepCategoryIdList).
		Where("task.plan_delivery_time between ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("r_step_category.link_id").Find(&qTotalRes).Error

	if err != nil {
		return
	}
	for _, re := range qTotalRes {
		upstreamDeliveryTotalCount[re.Id] = re.Number
	}

	// 交付总数
	var qDoneRes []types.GetPlanDepartmentCount
	dbr = ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Joins("join task_relation_tag r_episode on r_episode.task_id = task.id and r_episode.link_module_code in ('episode')").
		Select("count(distinct task.id) Number,r_step_category.link_id as Id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type in ('step', 'standard_make_to_do') and simpleJSONExtractInt(task.json, 'task_status') = 7").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("r_step_category.link_id in ?", upstreamStepCategoryIdList).
		Where("task.plan_delivery_time between ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("r_step_category.link_id").Find(&qDoneRes).Error

	if err != nil {
		return
	}
	for _, re := range qDoneRes {
		upstreamDeliveryCount[re.Id] = re.Number
	}

	// 实际交付总数
	var qActuallyDoneRes []types.GetPlanDepartmentCount
	dbr = ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Select("count(distinct task.id) Number,r_step_category.link_id as Id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type in ('step', 'standard_make_to_do') and simpleJSONExtractInt(task.json, 'task_status') = 7").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("r_step_category.link_id in ?", upstreamStepCategoryIdList).
		Where("task.delivery_time BETWEEN ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("r_step_category.link_id").Find(&qActuallyDoneRes).Error

	if err != nil {
		return
	}
	for _, re := range qActuallyDoneRes {
		upstreamDeliveryActuallyCount[re.Id] = re.Number
	}
	// 环节延期交付
	var qDelayDeliveryRes []types.GetPlanDepartmentCount
	dbr = ClickHouse.DB.Model(&model.DelayOrder{}).
		Joins("JOIN delay_order_task ON delay_order_task.delay_order_id = delay_order.id").
		Joins("JOIN review_request ON review_request.link_id = delay_order_task.id and review_request.module_id = ?", ModuleByCode("delay_order_task").Id).
		Select("count(distinct delay_order_task.task_id) as Number,delay_order.step_category_id as Id").
		Where("delay_order.step_category_id in ?", upstreamStepCategoryIdList).
		Where("review_request.is_pass = 'yes' and review_request.is_reviewed = 'yes'").
		Where("review_request.reviewed BETWEEN ? and ?", req.StartTime, req.EndTime)
	if req.ProjectId > 0 {
		dbr.Where("delay_order.project_id = ?", req.ProjectId)
	}
	err = dbr.Group("delay_order.step_category_id").Find(&qDelayDeliveryRes).Error

	if err != nil {
		return
	}

	// 环节延期交付
	var upstreamDelayDeliveryCount = make(map[int64]int64)
	for _, re := range qDelayDeliveryRes {
		upstreamDelayDeliveryCount[re.Id] = re.Number
	}

	var resContainer = types.GroupTaskCountUpstreamDeliveryResItem{}

	resContainer.UpstreamDeliveryNameMap = make(map[string]int)
	resContainer.UpstreamStepCategoryIdMap = make(map[int64]bool)
	resContainer.DownstreamStepCategoryIdMap = make(map[int64]bool)
	resContainer.DepartmentStepCategoryIdList = req.TodoStepCategoryIdList

	UpstreamDeliveryListIndex := 0

	for _, stepCategoryId := range req.TodoStepCategoryIdList {
		resContainer.DownstreamStepCategoryIdList = append(resContainer.UpstreamStepCategoryIdList, stepCategoryUpstreamDownstreamRelation[stepCategoryId].Downstream...)
		for _, upstreamStepCategoryId := range stepCategoryUpstreamDownstreamRelation[stepCategoryId].Upstream {
			if _, ok := resContainer.UpstreamStepCategoryIdMap[upstreamStepCategoryId]; ok {
				continue
			}
			resContainer.UpstreamStepCategoryIdList = append(resContainer.UpstreamStepCategoryIdList, upstreamStepCategoryId)
			resContainer.UpstreamStepCategoryIdMap[upstreamStepCategoryId] = true
			upstreamDeliveryTotal := upstreamDeliveryTotalCount[upstreamStepCategoryId]
			upstreamDeliveryActually := upstreamDeliveryActuallyCount[upstreamStepCategoryId]
			upstreamDelayDelivery := upstreamDelayDeliveryCount[upstreamStepCategoryId]
			upstreamDelivery := upstreamDeliveryCount[upstreamStepCategoryId]
			if upstreamDeliveryTotal == 0 && upstreamDeliveryActually == 0 && upstreamDelayDelivery == 0 {
				continue
			}
			showName := projectShowStepCategoryMap[upstreamStepCategoryId]
			if showName == "" {
				continue
			}
			if _, ok := resContainer.UpstreamDeliveryNameMap[showName]; ok {
				resContainer.UpstreamDeliveryDetail[resContainer.UpstreamDeliveryNameMap[showName]].DeliveryDone += upstreamDelivery
				resContainer.UpstreamDeliveryDetail[resContainer.UpstreamDeliveryNameMap[showName]].DeliveryTotal += upstreamDeliveryTotal
				resContainer.UpstreamDeliveryDetail[resContainer.UpstreamDeliveryNameMap[showName]].DeliveryActually += upstreamDeliveryActually
				resContainer.UpstreamDeliveryDetail[resContainer.UpstreamDeliveryNameMap[showName]].DelayDeliveryTotal += upstreamDelayDelivery // 环节延期交付
			} else {
				resContainer.UpstreamDeliveryDetail = append(resContainer.UpstreamDeliveryDetail, types.UpstreamDeliveryDetail{
					StepCategoryIdList: []int64{upstreamStepCategoryId},
					StepCategoryName:   showName,
					DeliveryDone:       upstreamDelivery,
					DeliveryTotal:      upstreamDeliveryTotal,
					DeliveryActually:   upstreamDeliveryActually,
					DelayDeliveryTotal: upstreamDelayDelivery, // 环节延期交付
				})
				resContainer.UpstreamDeliveryNameMap[showName] = UpstreamDeliveryListIndex
				UpstreamDeliveryListIndex += 1
			}
		}
	}
	res = resContainer.UpstreamDeliveryDetail

	if err != nil {
		return res, err
	}

	return
}

// getGroupPlanActuallyDoneCount 组装实际完成情况基础查询query
func (w *Workbench) getGroupPlanActuallyDoneCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader, userIdList []int64, stepType, notInStepType []string) (res []types.GetTaskPlanDepartmentCountItem, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:getGroupPlanActuallyDoneCount:%d%d%s%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)), strings.Join(stepType, ","))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()
	var rStepCategoryJoined = false
	var taskMemberJoined = false
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(distinct task.id) count,task.step_type step_type").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type <>'step_category'").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.end_time BETWEEN ? and ? and simpleJSONExtractInt(task.json,'task_status') IN (7,8)", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}

	if len(userIdList) > 0 {
		if !taskMemberJoined {
			dbr.Joins("JOIN task_member on task_member.task_id = task.id")
		}
		dbr.Where("task_member.partner in ?", userIdList)
	}

	if len(req.TodoStepCategoryIdList) > 0 {
		if !rStepCategoryJoined {
			dbr.Joins("JOIN task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')")
		}
		dbr.Where("r_step_category.link_id in ?", req.TodoStepCategoryIdList)
	}
	if len(stepType) > 0 {
		dbr.Where("task.step_type in ?", stepType)
	}

	if len(notInStepType) > 0 {
		dbr.Where("task.step_type not in ?", notInStepType)
	}

	err = dbr.Group("task.step_type").Find(&res).Error

	if err != nil {
		return res, err
	}
	return
}

// getGroupPlanDoneCountBaseQuery 组装完成情况基础查询query
func (w *Workbench) getGroupPlanDoneCountBaseQuery(req types.GroupTaskCountReq, userInfo types.UserInfoHeader, userIdList []int64) *gorm.DB {
	var rStepCategoryJoined = false
	var taskMemberJoined = false
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(distinct task.id) count,task.step_type step_type").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type <>'step_category'").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.plan_end_time BETWEEN ? and ?", req.StartTime, req.EndTime)

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}

	if len(userIdList) > 0 {
		if !taskMemberJoined {
			dbr.Joins("JOIN task_member on task_member.task_id = task.id")
		}
		dbr.Where("task_member.partner in ?", userIdList)
	}

	if len(req.TodoStepCategoryIdList) > 0 {
		if !rStepCategoryJoined {
			dbr.Joins("JOIN task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')")
		}
		dbr.Where("r_step_category.link_id in ?", req.TodoStepCategoryIdList)
	}

	dbr.Group("task.step_type")
	return dbr
}

// GroupPlanDoneCount 部门完成情况
func (w *Workbench) GroupPlanDoneCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader) (res types.GroupTaskCountPlanDoneResItem, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:GroupPlanDoneCount:%d%d%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()
	var qresTotal []types.TaskStepPlanDoneCount
	var userIdList []int64
	if req.DepartmentId > 0 {
		err = ClickHouse.DB.Model(&model.TenantUser{}).Joins("join user on user.union_id = tenant_user.user_id").
			Where("tenant_user.department_id = ?", req.DepartmentId).
			Select("distinct user.id uid").Pluck("uid", &userIdList).Error
		if err != nil || len(userIdList) == 0 {
			return res, err
		}
	}

	err = w.getGroupPlanDoneCountBaseQuery(req, userInfo, userIdList).
		Where("task.step_type in ?", []string{"step", "standard_make_to_do"}).
		Find(&qresTotal).Error

	if err != nil {
		return
	}

	for _, item := range qresTotal {
		res.TaskPlanTotal += item.Count
		res.TaskPlanTotalStep += item.Count
	}

	var qresDone []types.GetTaskPlanDepartmentCountItem

	err = w.getGroupPlanDoneCountBaseQuery(req, userInfo, userIdList).
		Where("task.step_type in ?", []string{"step", "standard_make_to_do"}).
		Where("simpleJSONExtractInt(task.json, 'task_status') IN (7,8)").
		Find(&qresDone).Error

	if err != nil {
		return
	}

	for _, item := range qresDone {
		res.TaskPlanDone += item.Count
		res.TaskPlanDoneStep += item.Count
	}

	// 实际完成情况查询
	qresActuallyDone, err := w.getGroupPlanActuallyDoneCount(req, userInfo, userIdList, []string{"step", "standard_make_to_do"}, nil)
	if err != nil {
		return
	}

	for _, item := range qresActuallyDone {
		res.TaskActuallyDone += item.Count
		res.TaskActuallyDoneStep += item.Count
	}

	// 标准任务/其他任务
	if len(req.TodoStepCategoryIdList) > 0 {
		userIdList = []int64{}
		stepCategoryIds := ""
		for _, i2 := range req.TodoStepCategoryIdList {
			stepCategoryIds += strconv.FormatInt(i2, 10) + ","
		}
		stepCategoryIds = strings.TrimRight(stepCategoryIds, ",")
		err = ClickHouse.DB.Model(&model.TenantUser{}).
			Joins("join user on user.union_id = tenant_user.user_id").
			Joins("join department on department.id = tenant_user.department_id").
			Where(fmt.Sprintf("hasAny(arrayMap(x->toInt64OrDefault(x),splitByChar(',',department.step_category_ids)),[%s])", stepCategoryIds)).
			Where("tenant_user.tenant_id = ?", userInfo.TenantId).
			Select("distinct user.id uid").Pluck("uid", &userIdList).Error
		if err != nil {
			return res, err
		}
	}

	if len(userIdList) > 0 {
		// 完成
		var qresOtherStandardDone []types.GetTaskPlanDepartmentCountItem
		req.TodoStepCategoryIdList = []int64{}
		err = w.getGroupPlanDoneCountBaseQuery(req, userInfo, userIdList).
			Where("task.step_type not in ?", []string{"step", "standard_make_to_do"}).
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7").
			Find(&qresOtherStandardDone).Error
		if err != nil {
			return res, err
		}
		for _, item := range qresOtherStandardDone {
			res.TaskPlanDone += item.Count
			if item.StepType == "standard_meeting" || item.StepType == "standard_review" {
				res.TaskPlanDoneStandardTodo += item.Count
			} else {
				//res.TaskPlanDoneOther += item.Count
			}
		}

		// 总数
		var qresOtherStandardTotal []types.GetTaskPlanDepartmentCountItem
		err = w.getGroupPlanDoneCountBaseQuery(req, userInfo, userIdList).
			Where("task.step_type not in ?", []string{"step", "standard_make_to_do"}).
			Find(&qresOtherStandardTotal).Error
		if err != nil {
			return res, err
		}
		for _, item := range qresOtherStandardTotal {
			res.TaskPlanTotal += item.Count
			if item.StepType == "standard_meeting" || item.StepType == "standard_review" {
				res.TaskPlanTotalStandardTodo += item.Count
			} else {
				//res.TaskPlanTotalOther += item.Count
			}
		}
		// 实际完成
		qresActuallyDone, err = w.getGroupPlanActuallyDoneCount(req, userInfo, userIdList, nil, []string{"step", "standard_make_to_do"})
		if err != nil {
			return res, err
		}

		for _, item := range qresActuallyDone {
			res.TaskActuallyDone += item.Count
			if item.StepType == "standard_meeting" || item.StepType == "standard_review" {
				res.TaskActuallyDoneStandardTodo += item.Count
			} else {
				//res.TaskActuallyDoneOther += item.Count
			}
		}
	}

	if err != nil {
		return res, err
	}
	return
}

func (w *Workbench) getGroupDeliveryCountBaseQuery(req types.GroupTaskCountReq, userInfo types.UserInfoHeader, userIdList []int64) *gorm.DB {
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("join task_relation_tag r_step_category on r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')").
		Joins("JOIN (select * from task_relation_tag where link_module_code in ('episode')) r_episode on r_episode.task_id = task.id").
		Where("task.tenant_id = ?", userInfo.TenantId).
		Where("task.step_type in ('step', 'standard_make_to_do')").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if req.ProjectId > 0 {
		dbr.Where("task.project_id = ?", req.ProjectId)
	}
	if len(userIdList) > 0 {
		dbr.Joins("join task_member on task_member.task_id = task.id").Where("task_member.partner in ?", userIdList)
	}

	if len(req.TodoStepCategoryIdList) > 0 {
		dbr.Where("r_step_category.link_id in ?", req.TodoStepCategoryIdList)
	}

	return dbr
}

// GroupDeliveryCount 部门交付情况
func (w *Workbench) GroupDeliveryCount(req types.GroupTaskCountReq, userInfo types.UserInfoHeader) (res types.GroupTaskCountDeliveryResItem, err error) {
	reqStr, _ := json.Marshal(req)
	rk := fmt.Sprintf("teamones-olap:GroupDeliveryCount:%d%d%s", userInfo.Id, userInfo.TenantId, teamonesHelper.GenerateMd5(string(reqStr)))
	err = GetCachedJson(rk, &res)
	if err == nil {
		return
	}
	err = nil
	defer func() {
		_ = SetCacheJsonExpire(rk, &res, time.Minute*3)
	}()

	var userIdList []int64
	if req.DepartmentId > 0 {
		err = ClickHouse.DB.Model(&model.TenantUser{}).
			Joins("join user on user.union_id = tenant_user.user_id").
			Where("tenant_user.department_id = ?", req.DepartmentId).
			Select("distinct user.id uid").Pluck("uid", &userIdList).Error
		if err != nil || len(userIdList) == 0 {
			return res, err
		}
	}

	if len(req.TodoStepCategoryIdList) == 0 && len(userIdList) == 0 {
		return
	}
	err = w.getGroupDeliveryCountBaseQuery(req, userInfo, userIdList).
		Where("task.plan_delivery_time between ? and ?", req.StartTime, req.EndTime).
		Select("count(distinct task.id) DeliveryDone").
		Pluck("DeliveryDone", &res.DeliveryTotal).Error

	if err != nil {
		return
	}

	err = w.getGroupDeliveryCountBaseQuery(req, userInfo, userIdList).
		Where("task.plan_delivery_time between ? and ?", req.StartTime, req.EndTime).
		Where("simpleJSONExtractInt(task.json, 'task_status') = 7").
		Select("count(distinct task.id) DeliveryTotal").
		Pluck("DeliveryTotal", &res.DeliveryDone).Error

	if err != nil {
		return res, err
	}

	err = w.getGroupDeliveryCountBaseQuery(req, userInfo, userIdList).
		Where("simpleJSONExtractInt(task.json, 'task_status') = 7 and task.delivery_time between ? and ?", req.StartTime, req.EndTime).
		Select("count(distinct task.id) DeliveryActually").
		Pluck("DeliveryActually", &res.DeliveryActually).Error

	if err != nil {
		return res, err
	}

	return
}

// EpisodeDetail 剧集详情
func (w *Workbench) EpisodeDetail(req types.EpisodeDetailReq, userInfo types.UserInfoHeader) (res types.EpisodeDetailRes, err error) {

	db := ClickHouse.DB.Model(&model.Entity{})

	if req.ProjectId > 0 {
		db.Where("project_id = ?", req.ProjectId)
	}

	err = db.Select("id episode_id,"+
		"name episode_name,"+
		"simpleJSONExtractString(json,'episode_custom_grade') as episode_custom_grade,"+
		"simpleJSONExtractInt(json,'episode_plan_seal_time') episode_plan_seal_time ,"+
		"simpleJSONExtractInt(json,'episode_seal_time') episode_seal_time").
		Where("simpleJSONExtractInt(json,'episode_plan_seal_time') between ? and ?", req.StartTime, req.EndTime).
		Order("episode_plan_seal_time,name").
		Find(&res).Error

	return
}

// 查询环节剧集详情
func (w *Workbench) queryStepCategoryEpisodeDetail(ctx *gin.Context, req types.StepCategoryInprogressEpisodeListReq, info types.UserInfoHeader, inputRes types.StepCategoryInprogressEpisodeListRes, needTimeFilter bool) (types.StepCategoryInprogressEpisodeListRes, error) {

	// 查询环节id对应大环节名称
	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return inputRes, err
	}
	currentShowName := ""
	projectShowStepCategoryMap := make(map[int64]string)
	for _, datum := range projectShowStepCategoryList.Data {
		projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName
	}

	for _, i2 := range req.StepCategoryIdList {
		if currentShowName != "" {
			break
		}
		if _, ok := projectShowStepCategoryMap[i2]; !ok {
			continue
		}
		currentShowName = projectShowStepCategoryMap[i2]
	}

	reviewOptions, err := optionService.GetProjectReviewOptions(ctx, req.ProjectId)
	if err != nil {
		return inputRes, err
	}

	reviewOptionMap := map[int64]string{}
	for _, reviewOptionItem := range reviewOptions {
		reviewOptionMap[reviewOptionItem.Id] = reviewOptionItem.Name
	}

	// 查询排期要完成的剧集
	var episodeIdList = make([]int64, len(inputRes))
	for i, re := range inputRes {
		episodeIdList[i] = re.EpisodeId
		if re.ReviewOptionsId != 0 {
			if reviewOptionName, ok := reviewOptionMap[re.ReviewOptionsId]; ok {
				inputRes[i].ReviewMeetingName = reviewOptionName
			}
		}
	}

	switch currentShowName {
	case "美术", "模型", "绑定", "关卡":

		var prepareNameList []string
		switch currentShowName {
		case "美术":
			prepareNameList = []string{"角色", "道具", "关卡", "场景"}
		case "模型":
			prepareNameList = []string{"角色", "道具", "场景"}
		case "绑定":
			prepareNameList = []string{"角色", "道具"}
		case "关卡":
			prepareNameList = []string{"关卡"}
		}
		// 查询剧集任务实体数量
		var episodeEntityCountList []types.EpisodeAssetTaskCountDetail
		etq := ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN entity on entity.id = task.entity_id join module on module.id = entity.module_id").
			Where("task.step_type = 'step' and task.episode_id in ?", episodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

		if needTimeFilter {
			etq.Where("task.plan_end_time between ? and ?", req.StartTime, req.EndTime)
		}

		if req.ProjectId > 0 {
			etq = etq.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			etq = etq.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}

		err = etq.Select("task.episode_id episode_id," +
			"multiIf(module.code='design', simpleJSONExtractString(entity.json, 'design_category'), module.code = 'asset'," +
			"simpleJSONExtractString(entity.json, 'asset_category')," +
			"simpleJSONExtractString(entity.json, 'level_category')) category_name," +
			"count(distinct task.entity_id) entity_number").
			Group("task.episode_id,category_name").
			Find(&episodeEntityCountList).Error
		if err != nil {
			return inputRes, err
		}
		var episodeEntityCountListMap = make(map[string]*types.EpisodeAssetTaskCountDetail)
		for i, detail := range episodeEntityCountList {
			episodeEntityCountListMap[fmt.Sprintf("%d-%s", detail.EpisodeId, detail.CategoryName)] = &episodeEntityCountList[i]
		}

		// 查询剧集任务实体数量
		var episodeTaskDetailCountList []types.EpisodeAssetTaskCountDetail
		etq = ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN entity on entity.id = task.entity_id join module on module.id = entity.module_id").
			Where("task.step_type = 'step' and task.episode_id in ?", episodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

		if req.ProjectId > 0 {
			etq = etq.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			etq = etq.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}

		err = etq.Select("task.episode_id episode_id," +
			"multiIf(module.code='design', simpleJSONExtractString(entity.json, 'design_category'), module.code = 'asset'," +
			"simpleJSONExtractString(entity.json, 'asset_category')," +
			"simpleJSONExtractString(entity.json, 'level_category')) category_name," +
			"count(task.id) total," +
			"count(simpleJSONExtractInt(task.json,'task_status') in (7,8) or null) done").
			Group("task.episode_id,category_name").
			Find(&episodeTaskDetailCountList).Error
		if err != nil {
			return inputRes, err
		}

		var episodeTaskDetailCountListMap = make(map[string]*types.EpisodeAssetTaskCountDetail)

		for i, detail := range episodeTaskDetailCountList {
			episodeTaskDetailCountListMap[fmt.Sprintf("%d-%s", detail.EpisodeId, detail.CategoryName)] = &episodeTaskDetailCountList[i]
		}

		for i, re := range inputRes {
			for categoryNameIndex, categoryName := range prepareNameList {
				inputRes[i].AssetTaskCountDetailList = append(inputRes[i].AssetTaskCountDetailList, types.EpisodeAssetTaskCountDetail{
					EpisodeId:    re.EpisodeId,
					CategoryName: categoryName,
					Total:        0,
					Done:         0,
					EntityNumber: 0,
				})

				var k = fmt.Sprintf("%d-%s", re.EpisodeId, categoryName)

				if _, ok := episodeEntityCountListMap[k]; ok {
					inputRes[i].AssetTaskCountDetailList[categoryNameIndex].EntityNumber = episodeEntityCountListMap[k].EntityNumber
				}

				if _, ok := episodeTaskDetailCountListMap[k]; ok {

					inputRes[i].AssetTaskCountDetailList[categoryNameIndex].Total = episodeTaskDetailCountListMap[k].Total
					inputRes[i].AssetTaskCountDetailList[categoryNameIndex].Done = episodeTaskDetailCountListMap[k].Done
					inputRes[i].TaskTotal += episodeTaskDetailCountListMap[k].Total
					inputRes[i].TaskDone += episodeTaskDetailCountListMap[k].Done
				}

			}
		}

	case "动画", "解算", "灯光", "特效", "合成":
		// 查询剧集任务实体数量
		var episodeTaskCountList []types.EpisodeTaskCountDetail

		etq := ClickHouse.DB.Model(&model.Task{}).
			Where("task.step_type = 'step' and task.episode_id in ?", episodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

		if req.ProjectId > 0 {
			etq = etq.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			etq = etq.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}

		err = etq.Select("task.episode_id episode_id," +
			"count(task.id) total," +
			"count(simpleJSONExtractInt(task.json,'task_status') in (7,8) or null) done").
			Group("task.episode_id").
			Find(&episodeTaskCountList).Error
		if err != nil {
			return inputRes, err
		}
		var episodeTaskCountMap = make(map[int64]*types.EpisodeTaskCountDetail)

		for i, detail := range episodeTaskCountList {
			episodeTaskCountMap[detail.EpisodeId] = &episodeTaskCountList[i]
		}

		// 查询剧集任务实体数量
		var episodeEntityCountList []types.EpisodeTaskCountDetail

		etq = ClickHouse.DB.Model(&model.Task{}).
			Where("task.step_type = 'step' and task.episode_id in ?", episodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

		if req.ProjectId > 0 {
			etq = etq.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			etq = etq.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}

		if needTimeFilter {
			etq.Where("task.plan_end_time between ? and ?", req.StartTime, req.EndTime)
		}

		err = etq.Select("task.episode_id episode_id," +
			"count(distinct task.entity_id) entity_number").
			Group("task.episode_id").
			Find(&episodeEntityCountList).Error
		if err != nil {
			return inputRes, err
		}
		var episodeEntityCountMap = make(map[int64]*types.EpisodeTaskCountDetail)

		for i, detail := range episodeEntityCountList {
			episodeEntityCountMap[detail.EpisodeId] = &episodeEntityCountList[i]
		}

		for i, re := range inputRes {
			if _, ok := episodeEntityCountMap[re.EpisodeId]; ok {
				inputRes[i].EntityTotal = episodeEntityCountMap[re.EpisodeId].EntityNumber
			}
			if _, ok := episodeTaskCountMap[re.EpisodeId]; ok {
				inputRes[i].TaskTotal = episodeTaskCountMap[re.EpisodeId].Total
				inputRes[i].TaskDone = episodeTaskCountMap[re.EpisodeId].Done
			}
		}
	case "分镜", "剧本", "动捕", "成片":
	default:

	}

	// 查询剧集基本信息
	var episodeList []model.Episode
	err = ClickHouse.DB.Model(&model.Entity{}).Where("id in ?", episodeIdList).
		Select("id,name,simpleJSONExtractString(json,'episode_custom_grade') episode_custom_grade").
		Find(&episodeList).Error
	if err != nil {
		return inputRes, err
	}
	var episodeMap = make(map[int64]*model.Episode)
	for i, episode := range episodeList {
		episodeMap[int64(episode.Id)] = &episodeList[i]
	}

	for i, re := range inputRes {
		if _, ok := episodeMap[re.EpisodeId]; ok {
			inputRes[i].EpisodeName = episodeMap[re.EpisodeId].Name
			inputRes[i].EpisodeCustomGrade = episodeMap[re.EpisodeId].EpisodeCustomGrade
		}
	}

	return inputRes, err

}

// StepCategoryInprogressEpisodeList 环节进行中剧集列表
func (w *Workbench) StepCategoryInprogressEpisodeList(ctx *gin.Context, req types.StepCategoryInprogressEpisodeListReq, info types.UserInfoHeader) (res types.StepCategoryInprogressEpisodeListRes, err error) {
	// 查询这些剧集里今天有审核排期的
	var reviewEpisodeIdList []int64
	var todayStart = time.Now().Format("2006-01-02")
	todayStartUnix, err := time.ParseInLocation("2006-01-02", todayStart, time.Local)
	if err != nil {
		return res, err
	}
	ts := todayStartUnix.Unix()
	te := ts + 60*60*24 - 1
	previewQ := ClickHouse.DB.Model(&model.Plan{}).
		Joins("JOIN task on task.id = plan.task_id").
		Joins("JOIN task_relation_tag r_step_category on r_step_category.link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id").
		Joins("JOIN task_relation_tag r_episode on r_episode.link_module_code in ('episode') and r_episode.task_id = plan.task_id").
		Where("plan.end_time <= ? and simpleJSONExtractInt(task.json,'task_status') not in (3,7)", te).
		Where("plan.type = 'review'")
	if req.ProjectId > 0 {
		previewQ.Where("plan.project_id = ?", req.ProjectId)
	}
	if len(req.StepCategoryIdList) > 0 {
		previewQ.Where("r_step_category.link_id in ?", req.StepCategoryIdList)
	}
	err = previewQ.Select("distinct r_episode.link_id episode_id").Pluck("episode_id", &reviewEpisodeIdList).Error
	if err != nil {
		return
	}

	var sealEpisodeIdList []int64
	// 查询本月封板了的环节剧集
	sealQ := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Where("episode_step_category_seal_time.seal_time between 1 and ?", time.Now().Unix()).
		Select("arrayJoin(arrayMap(x->toInt64OrDefault(x),splitByChar(',', step_category_ids))) step_category_id,entity_id")
	if req.ProjectId > 0 {
		sealQ.Where("episode_step_category_seal_time.project_id = ?", req.ProjectId)
	}
	if len(req.StepCategoryIdList) > 0 {
		sealQ.Where("step_category_id in ?", req.StepCategoryIdList)
	}

	err = ClickHouse.DB.Table("(?) as q", sealQ).
		Select("distinct entity_id as episode_id").
		Pluck("episode_id", &sealEpisodeIdList).Error
	if err != nil {
		return
	}

	// 查询排期要完成的剧集
	pq := ClickHouse.DB.Model(&model.Plan{}).
		Joins("JOIN entity on entity.id = plan.entity_id").
		Joins("JOIN task_relation_tag r_step_category on link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id").
		Where("plan.scope = 'single' and plan.type = 'step_category'")
	if req.ProjectId > 0 {
		pq = pq.Where("plan.project_id = ?", req.ProjectId)
	}
	if len(reviewEpisodeIdList) > 0 {
		pq = pq.Where("plan.entity_id not in ?", reviewEpisodeIdList)
	}
	if len(sealEpisodeIdList) > 0 {
		pq = pq.Where("plan.entity_id not in ?", sealEpisodeIdList)
	}
	pq = pq.Where("plan.start_time <= ? and plan.end_time >= ?", req.EndTime, req.StartTime)
	if len(req.StepCategoryIdList) > 0 {
		pq = pq.Where("r_step_category.link_id in ?", req.StepCategoryIdList)
	}
	err = pq.Group("entity.id,entity.code").
		Select("entity.id as episode_id,entity.code as episode_code,min(plan.start_time) min_plan_start_time,max(plan.end_time) max_plan_end_time").
		Order("entity.code ASC").
		Find(&res).Error

	if err != nil || len(res) == 0 {
		return res, err
	}

	res, err = w.queryStepCategoryEpisodeDetail(ctx, req, info, res, true)

	return
}

// StepCategoryReviewingEpisodeList 环节审核中的剧集情况
func (w *Workbench) StepCategoryReviewingEpisodeList(ctx *gin.Context, req types.StepCategoryInprogressEpisodeListReq, info types.UserInfoHeader) (res types.StepCategoryInprogressEpisodeListRes, err error) {
	// 查询这些剧集里今天有审核排期的
	var todayStart = time.Now().Format("2006-01-02")
	todayStartUnix, err := time.ParseInLocation("2006-01-02", todayStart, time.Local)
	if err != nil {
		return res, err
	}
	ts := todayStartUnix.Unix()
	te := ts + 60*60*24 - 1

	var sealEpisodeIdList []int64
	// 查询本月封板了的环节剧集
	sealQ := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Where("episode_step_category_seal_time.seal_time between 1 and ?", time.Now().Unix()).
		Select("arrayJoin(arrayMap(x->toInt64OrDefault(x),splitByChar(',', step_category_ids))) step_category_id,entity_id")
	if req.ProjectId > 0 {
		sealQ.Where("episode_step_category_seal_time.project_id = ?", req.ProjectId)
	}
	if len(req.StepCategoryIdList) > 0 {
		sealQ.Where("step_category_id in ?", req.StepCategoryIdList)
	}

	err = ClickHouse.DB.Table("(?) as q", sealQ).
		Select("distinct entity_id as episode_id").
		Pluck("episode_id", &sealEpisodeIdList).Error
	if err != nil {
		return
	}

	previewQ := ClickHouse.DB.Model(&model.Plan{}).
		Joins("JOIN task on task.id = plan.task_id").
		Joins("JOIN task_relation_tag r_step_category on r_step_category.link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id").
		Joins("JOIN task_relation_tag r_episode on r_episode.link_module_code in ('episode') and r_episode.task_id = plan.task_id").
		Joins("JOIN entity on entity.id = r_episode.link_id").
		Where("plan.end_time <= ? and simpleJSONExtractInt(task.json,'task_status') not in (3,7)", te).
		Where("plan.type = 'review'")
	if req.ProjectId > 0 {
		previewQ.Where("plan.project_id = ?", req.ProjectId)
	}
	if len(req.StepCategoryIdList) > 0 {
		previewQ.Where("r_step_category.link_id in ?", req.StepCategoryIdList)
	}
	if len(sealEpisodeIdList) > 0 {
		previewQ = previewQ.Where("r_episode.link_id not in ?", sealEpisodeIdList)
	}
	// todo 选择了标签的 显示标签名称 没选的  直接展示待办名称
	err = previewQ.Group("entity.id,entity.code").
		Select("entity.id as episode_id,entity.code as episode_code,min(plan.start_time) min_plan_start_time,max(plan.end_time) max_plan_end_time,min(task.name) as review_meeting_name, max(simpleJSONExtractInt(task.json, 'task_project_review_options_id')) as review_options_id").
		Order("entity.code").
		Find(&res).Error

	if err != nil || len(res) == 0 {
		return res, err
	}

	res, err = w.queryStepCategoryEpisodeDetail(ctx, req, info, res, false)
	return
}

// StepCategorySealEpisodeList 环节封板剧集情况查询
func (w *Workbench) StepCategorySealEpisodeList(ctx *gin.Context, req types.StepCategoryInprogressEpisodeListReq, userInfo types.UserInfoHeader) (res types.StepCategoryInprogressEpisodeListRes, err error) {
	// 查询本月封板了的环节剧集
	previewQ := ClickHouse.DB.Model(&model.EpisodeStepCategorySealTime{}).
		Joins("JOIN entity on entity.id = episode_step_category_seal_time.entity_id").
		Where("episode_step_category_seal_time.seal_time between ? and ?", req.StartTime, time.Now().Unix()).
		Select("arrayJoin(arrayMap(x->toInt64OrDefault(x),splitByChar(',', step_category_ids))) step_category_id,entity.id as episode_id,entity.code as episode_code")
	if req.ProjectId > 0 {
		previewQ.Where("episode_step_category_seal_time.project_id = ?", req.ProjectId)
	}
	if len(req.StepCategoryIdList) > 0 {
		previewQ.Where("step_category_id in ?", req.StepCategoryIdList)
	}

	err = ClickHouse.DB.Table("(?) as q", previewQ).
		Group("episode_id,episode_code").
		Select("episode_id,episode_code").
		Order("episode_code").
		Find(&res).Error

	if err != nil || len(res) == 0 {
		return res, err
	}
	res, err = w.queryStepCategoryEpisodeDetail(ctx, req, userInfo, res, false)

	return
}

// EpisodeSealShowList 剧集封板播出情况列表
func (w *Workbench) EpisodeSealShowList(req types.EpisodeSealShowListReq) (res types.EpisodeSealShowListRes, err error) {

	res = make(map[string]*[]types.EpisodeSealShowListResItem)

	var planShow []types.EpisodeSealShowListResItem
	var planSeal []types.EpisodeSealShowListResItem
	res["plan_seal"] = &planSeal
	res["plan_show"] = &planShow
	err = ClickHouse.DB.Model(&model.Episode{}).
		Where("project_id = ? and module_id = ? and episode_plan_seal_time between ? and ?", req.ProjectId, ModuleByCode("episode").Id, req.StartTime, req.EndTime).
		Select("id as episode_id," +
			"name as episode_name," +
			"simpleJSONExtractInt(json, 'episode_plan_seal_time') episode_plan_seal_time , " +
			"simpleJSONExtractInt(json, 'episode_plan_show_time') episode_plan_show_time").
		Find(&planSeal).
		Error
	if err != nil {
		return
	}
	err = ClickHouse.DB.Model(&model.Episode{}).
		Where("project_id = ? and module_id = ? and episode_plan_show_time between ? and ?", req.ProjectId, ModuleByCode("episode").Id, req.StartTime, req.EndTime).
		Select("id as episode_id," +
			"name as episode_name," +
			"simpleJSONExtractInt(json, 'episode_plan_seal_time') episode_plan_seal_time , " +
			"simpleJSONExtractInt(json, 'episode_plan_show_time') episode_plan_show_time").
		Find(&planShow).
		Error
	return

}

// MakingOverview 制作情况概览
func (w *Workbench) MakingOverview(ctx *gin.Context, req types.MakingOverviewReq) (res types.MakingOverviewRes, err error) {

	var planEpisodeIdList []int64
	// 查询排期要完成的剧集
	pq := ClickHouse.DB.Model(&model.Plan{}).
		Joins("JOIN entity on entity.id = plan.entity_id").
		Joins("JOIN task_relation_tag r_step_category on link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id").
		Where("plan.scope = 'single' and plan.type = 'step_category'")
	if req.ProjectId > 0 {
		pq = pq.Where("plan.project_id = ?", req.ProjectId)
	}

	pq = pq.Where("plan.start_time <= ? and plan.end_time >= ?", req.EndTime, req.StartTime)
	if len(req.StepCategoryIdList) > 0 {
		pq = pq.Where("r_step_category.link_id in ?", req.StepCategoryIdList)
	}
	err = pq.Select("entity.id").Pluck("entity.id", &planEpisodeIdList).Error

	if err != nil || len(planEpisodeIdList) == 0 {
		return
	}

	// 查询环节id对应大环节名称
	var optionService Option
	projectShowStepCategoryList, err := optionService.GetProjectShowStepCategoryListConfig(ctx)
	if err != nil {
		return
	}
	currentShowName := ""
	projectShowStepCategoryMap := make(map[int64]string)
	for _, datum := range projectShowStepCategoryList.Data {
		projectShowStepCategoryMap[int64(datum.ID)] = datum.ShowName
	}

	for _, i2 := range req.StepCategoryIdList {
		if currentShowName != "" {
			break
		}
		if _, ok := projectShowStepCategoryMap[i2]; !ok {
			continue
		}
		currentShowName = projectShowStepCategoryMap[i2]
	}

	switch currentShowName {
	case "美术", "模型", "绑定", "关卡":
		var prepareNameList []string
		switch currentShowName {
		case "美术":
			prepareNameList = []string{"角色", "道具", "关卡", "场景"}
		case "模型":
			prepareNameList = []string{"角色", "道具", "场景"}
		case "绑定":
			prepareNameList = []string{"角色", "道具"}
		case "关卡":
			prepareNameList = []string{"关卡"}
		}
		nameMap := make(map[string]int)
		for i, categoryName := range prepareNameList {
			res.AssetMakeCategoryNumberList = append(res.AssetMakeCategoryNumberList, types.AssetMakeCategoryNumberDetail{Name: categoryName})
			nameMap[categoryName] = i
		}

		var episodeTaskDetailCountList []types.AssetMakeCategoryNumberDetail
		etq := ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN entity on entity.id = task.entity_id join module on module.id = entity.module_id").
			Where("task.step_type = 'step' and task.episode_id in ?", planEpisodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.plan_end_time between ? and ?", req.StartTime, req.EndTime)

		if req.ProjectId > 0 {
			etq = etq.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			etq = etq.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}

		err = etq.Select("multiIf(module.code='design', simpleJSONExtractString(entity.json, 'design_category'), module.code = 'asset'," +
			"simpleJSONExtractString(entity.json, 'asset_category')," +
			"simpleJSONExtractString(entity.json, 'level_category')) name," +
			"count(distinct task.entity_id) number").
			Group("name").
			Find(&episodeTaskDetailCountList).Error
		if err != nil {
			return res, err
		}

		for _, detail := range episodeTaskDetailCountList {
			if _, ok := nameMap[detail.Name]; !ok {
				continue
			}
			res.AssetMakeCategoryNumberList[nameMap[detail.Name]].Number = detail.Number
		}
	case "动画", "解算", "灯光", "特效", "合成":

		// 查询本月制作总镜头数
		etq := ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN entity on entity.id = task.entity_id join module on module.id = entity.module_id").
			Where("task.step_type = 'step' and task.episode_id in ?", planEpisodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.plan_end_time between ? and ?", req.StartTime, req.EndTime)

		if req.ProjectId > 0 {
			etq = etq.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			etq = etq.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}
		err = etq.Select("count(distinct task.entity_id) number").Find(&res.MakeEntityTotal).Error
		if err != nil {
			return
		}

		// 查询审核记录
		var reviewEpisodeList []types.ReviewTagNameEpisodeList
		previewQ := ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN task_relation_tag r_step_category on r_step_category.link_module_code in ('step_category','show_step_category') and r_step_category.task_id = task.id").
			Joins("JOIN task_relation_tag r_episode on r_episode.link_module_code in ('episode') and r_episode.task_id = task.id").
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.plan_end_time <= ? and r_episode.link_id in ?", req.EndTime, planEpisodeIdList).
			Where("task.step_type = 'standard_review'")
		if req.ProjectId > 0 {
			previewQ.Where("task.project_id = ?", req.ProjectId)
		}
		if len(req.StepCategoryIdList) > 0 {
			previewQ.Where("r_step_category.link_id in ?", req.StepCategoryIdList)
		}
		err = previewQ.Select("simpleJSONExtractInt(json, 'task_project_review_options_id') review_options_id,r_episode.link_id episode_id").
			Group("episode_id,review_options_id").
			Find(&reviewEpisodeList).Error

		if err != nil || len(reviewEpisodeList) == 0 {
			return res, err
		}

		tagConfig, err := optionService.GetProjectReviewOptions(ctx, req.ProjectId)
		if err != nil {
			return res, err
		}
		tagConfigMap := make(map[int64]types.GetProjectReviewOptionsResData)

		for _, datum := range tagConfig {
			tagConfigMap[datum.Id] = datum
		}
		var reviewEpisodeIdList []int64
		var reviewEpisodeIdMap = make(map[int64]bool)
		var reviewEpisodeListGroupedByName = make(map[int64][]int64)
		for _, list := range reviewEpisodeList {
			if _, ok := reviewEpisodeIdMap[list.EpisodeId]; !ok {
				reviewEpisodeIdList = append(reviewEpisodeIdList, list.EpisodeId)
			}
			reviewEpisodeListGroupedByName[list.ReviewOptionsId] = append(reviewEpisodeListGroupedByName[list.ReviewOptionsId], list.EpisodeId)
		}
		var episodeEntityTotal []types.EpisodeEntityCount

		episodeEntityCountQuery := ClickHouse.DB.Model(&model.Task{}).
			Joins("JOIN entity on entity.id = task.entity_id join module on module.id = entity.module_id").
			Where("task.step_type = 'step' and task.episode_id in ?", planEpisodeIdList).
			Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
			Where("task.episode_id in ?", reviewEpisodeIdList)

		if len(req.StepCategoryIdList) > 0 {
			episodeEntityCountQuery = episodeEntityCountQuery.Where("task.step_category_id in ?", req.StepCategoryIdList)
		}
		err = episodeEntityCountQuery.Select("count(distinct task.entity_id) EntityNumber,task.episode_id EpisodeId").
			Group("task.episode_id").
			Find(&episodeEntityTotal).Error
		if err != nil {
			return res, err
		}
		var episodeList []model.Episode

		err = ClickHouse.DB.Model(&model.Episode{}).Where("id in ?", reviewEpisodeIdList).Select("id,name,code").Find(&episodeList).Error
		if err != nil {
			return res, err
		}

		var episodeMap = make(map[int64]*model.Episode)
		for ei, episode := range episodeList {
			episodeMap[int64(episode.Id)] = &episodeList[ei]
		}

		episodeEntityTotalMap := make(map[int64]int64)

		for _, count := range episodeEntityTotal {
			episodeEntityTotalMap[count.EpisodeId] = count.EntityNumber
		}

		for reviewOptionsId, episodeIdList := range reviewEpisodeListGroupedByName {
			if reviewOptionsId == 0 {
				// tag 为空
				continue
			}
			// todo 查询配置 使用id
			tagConfigValue, ok := tagConfigMap[reviewOptionsId]
			if !ok {
				// tag 找不到配置
				continue
			}
			var tempMakingOverviewReviewDetailEpisodeList []types.MakingOverviewReviewDetailEpisode
			for _, episodeId := range episodeIdList {
				if _, ok := episodeMap[episodeId]; !ok {
					continue
				}
				tempMakingOverviewReviewDetailEpisodeList = append(tempMakingOverviewReviewDetailEpisodeList, types.MakingOverviewReviewDetailEpisode{
					EpisodeId:    episodeId,
					EpisodeName:  episodeMap[episodeId].Name,
					EntityNumber: episodeEntityTotalMap[episodeId],
				})
			}
			res.ReviewDetailList = append(res.ReviewDetailList, types.MakingOverviewReviewDetail{
				Name:          tagConfigValue.Name,
				ChangePercent: IntVal(tagConfigMap[reviewOptionsId].Json[0].RepairPercent),
				EpisodeList:   tempMakingOverviewReviewDetailEpisodeList,
			})
		}

	default:
		res.MakeEpisodeNumber = int64(len(planEpisodeIdList))
	}

	return
}
